pandas, SQL
Задача – подготовить аналитический отчет для HR-отдела. На основании проведенной аналитики предполагается составить рекомендации для отдела кадров по стратегии набора персонала, а также по взаимодействию с уже имеющимися сотрудниками.
В базе данных лежит набор таблиц, которые содержат данные о сотрудниках вымышленной компании.
Обзор текущей ситуации
1.1. Гендерный состав департаментов компании
1.2. Гендерный и возрастной состав департаментов компании
1.3. Расовый состав департаментов компании
1.4. Семейное положение сотрудников департамента компании
1.5. Краткая информация со статистикой по департаментам компании
1.6. Агрегирующие показатели по заработной плате департаментов
1.7. Бюджеты подразделений компании
1.8. Разброс заработных плат внутри должностей
1.9. Зависимость между полом, расой, возрастом и заработной платой сотрудника
Выводы по блоку 1
Влияние характеристик сотрудника на оценку перформанса и продолжительность работы
2.1. Зависимость между сроком работы в компании, полом и возрастом найма сотрудника
2.2. Зависимость между сроком работы в компании, полом и семейным положением сотрудника
2.3. Зависимость между сроком работы в компании и расой сотрудника
2.4. Зависимость между оценкой перформанса и полом сотрудника
2.5. Зависимость между оценкой перформанса и возрастом сотрудника
2.6. Зависимость между оценкой перформанса и расой сотрудника
2.7. Зависимость между оценкой перформанса и семейным положением сотрудника
2.8. Зависимость между оценкой перформанса и стажем сотрудника
Выводы по блоку 2
Влияние работодателя на продолжительность работы сотрудников в компании и на качество перформанса
3.1. Продолжительность работы сотрудников в компании по департаментам
3.2. Продолжительность работы сотрудников в компании в зависимости от руководителя
3.3. Продолжительность работы сотрудников в компании в зависимости от уровня оплаты
3.4. Оценка перформанса сотрудника в разбивке по департаментам
3.5. Оценка перформанса сотрудника в зависимости от руководителя
3.6. Оценка перформанса сотрудника в зависимости от оплаты труда и департамента
Выводы по блоку 3
Обзор причин ухода из компании и возможности работодателя влиять на них
4.1. Три группы ушедших: не можем повлиять, можем повлиять, постараться не брать
4.2. Причины ухода в зависимости от департаментов
4.3. Аналитика группы сотрудников, на чей уход мы можем повлиять
4.4. Аналитика группы сотрудников, которых стоит постараться не набирать исходя из причин ухода из компани
4.5. Типичный портрет человека, которого не стоит нанимать
4.6. Ушедшие сотрудники категории "не стоило нанимать" по руководителям
4.7. Анализ текутести персонала по компании, отделам, менеджерам
Выводы по блоку 4
Обзор источников найма и их качества
5.1. Метрика оценки "качества" нанятого сотрудника
5.2. Полезность источников найма (суммарное "качество" нанятых)
5.3. Оплачиваемые и неоплачиваемые источники найма
5.4. Эффективность оплачиваемых источников найма
Выводы по блоку 5
# импортируем необходимые библиотеки
import sqlalchemy
import psycopg2
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import matplotlib.font_manager
# зададим параметры форматирования графиков
font_path = 'C:\\learn\\Anaconda\\Lib\\site-packages\\matplotlib\\mpl-data\\fonts\\ttf\\Montserrat-Black.ttf'
matplotlib.font_manager.fontManager.addfont(font_path)
font_path_2 = 'C:\\learn\\Anaconda\\Lib\\site-packages\\matplotlib\\mpl-data\\fonts\\ttf\\Montserrat-Regular.ttf'
matplotlib.font_manager.fontManager.addfont(font_path_2)
font_properties = matplotlib.font_manager.FontProperties(fname=font_path)
params = {
'axes.titlepad': 29,
'axes.titleweight': 'bold',
'axes.titlesize': 14,
'axes.titlelocation': 'left',
'axes.labelsize': 12,
'figure.titlesize': 18,
'figure.titleweight': 'bold',
'legend.fontsize': 12,
'legend.title_fontsize': 12,
'legend.frameon': False,
'image.cmap': 'magma',
'axes.spines.right': False,
'axes.spines.top': False,
'figure.figsize': (12, 6),
'axes.labelpad': 10,
'axes.grid.axis': 'y',
'grid.alpha': 0.3,
'grid.linewidth': 0.3,
'grid.color': 'black',
'axes.grid.which': 'both',
'font.family': 'Montserrat'
}
plt.rcParams.update(params)
# функция для отрисовки горизонтальных линий - украшений графиков
def upper_line(ax, is_suptitle=False):
if is_suptitle:
ax.plot([.05, 1], [1.01, 1.01], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
ax.plot([.05, 1], [0.94, .94], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
else:
ax.plot([0.05, 0.9], [0.99, 0.99], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
ax.plot([0.05, 0.9], [0.90, 0.90], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
# создадим объект engine для взаимодействия с БД
engine = sqlalchemy.create_engine('postgresql+psycopg2://readonly:6hajV34RTQfmxhS@dsstudents.skillbox.ru:5432/human_resources')
# создадим объект соединения
connect = engine.connect()
# создадим объект inspection, чтобы вывести названия столбцов в таблицах
inspector = sqlalchemy.inspect(engine)
# Выведем названия и типы столбцов в каждой таблице, а также количество строк в каждой таблице
tables = inspector.get_table_names()
for table in tables:
print('\n___________________________________')
print(f'Таблица {table}:')
print('Кол-во рядов', connect.execute(f'SELECT count(*) FROM {table}').scalar())
head = pd.read_sql(f'SELECT * FROM {table} LIMIT 5', connect)
display(head)
for column in inspector.get_columns(table):
print('Название:\t', column['name'], '\t\tТип:\t', column['type'])
___________________________________ Таблица hr_dataset: Кол-во рядов 310
| id | Employee Name | Employee Number | marriedid | maritalstatusid | genderid | empstatus_id | deptid | perf_scoreid | age | ... | Date of Hire | Days Employed | Date of Termination | Reason For Term | Employment Status | department | position | Manager Name | Employee Source | Performance Score | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | Brown, Mia | 1103024456 | 1 | 1 | 0 | 1 | 1 | 3 | 30 | ... | 2008-10-27 | 3317 | None | N/A - still employed | Active | Admin Offices | Accountant I | Brandon R. LeBlanc | Diversity Job Fair | Fully Meets |
| 1 | 2 | LaRotonda, William | 1106026572 | 0 | 2 | 1 | 1 | 1 | 3 | 34 | ... | 2014-01-06 | 1420 | None | N/A - still employed | Active | Admin Offices | Accountant I | Brandon R. LeBlanc | Website Banner Ads | Fully Meets |
| 2 | 3 | Steans, Tyrone | 1302053333 | 0 | 0 | 1 | 1 | 1 | 3 | 31 | ... | 2014-09-29 | 1154 | None | N/A - still employed | Active | Admin Offices | Accountant I | Brandon R. LeBlanc | Internet Search | Fully Meets |
| 3 | 4 | Howard, Estelle | 1211050782 | 1 | 1 | 0 | 1 | 1 | 9 | 32 | ... | 2015-02-16 | 58 | 2015-04-15 | N/A - still employed | Active | Admin Offices | Administrative Assistant | Brandon R. LeBlanc | Pay Per Click - Google | N/A- too early to review |
| 4 | 5 | Singh, Nan | 1307059817 | 0 | 0 | 0 | 1 | 1 | 9 | 30 | ... | 2015-05-01 | 940 | None | N/A - still employed | Active | Admin Offices | Administrative Assistant | Brandon R. LeBlanc | Website Banner Ads | N/A- too early to review |
5 rows × 29 columns
Название: id Тип: INTEGER Название: Employee Name Тип: VARCHAR(50) Название: Employee Number Тип: INTEGER Название: marriedid Тип: INTEGER Название: maritalstatusid Тип: INTEGER Название: genderid Тип: INTEGER Название: empstatus_id Тип: INTEGER Название: deptid Тип: INTEGER Название: perf_scoreid Тип: INTEGER Название: age Тип: INTEGER Название: Pay Rate Тип: REAL Название: state Тип: CHAR(2) Название: zip Тип: INTEGER Название: dob Тип: DATE Название: sex Тип: VARCHAR(10) Название: maritaldesc Тип: VARCHAR(50) Название: citizendesc Тип: VARCHAR(50) Название: Hispanic/Latino Тип: VARCHAR(3) Название: racedesc Тип: VARCHAR(50) Название: Date of Hire Тип: DATE Название: Days Employed Тип: INTEGER Название: Date of Termination Тип: DATE Название: Reason For Term Тип: VARCHAR(50) Название: Employment Status Тип: VARCHAR(50) Название: department Тип: VARCHAR(50) Название: position Тип: VARCHAR(50) Название: Manager Name Тип: VARCHAR(50) Название: Employee Source Тип: VARCHAR(50) Название: Performance Score Тип: VARCHAR(50) ___________________________________ Таблица production_staff: Кол-во рядов 256
| id | Employee Name | Race Desc | Date of Hire | TermDate | Reason for Term | Employment Status | Department | Position | Pay | Manager Name | Performance Score | Abutments/Hour Wk 1 | Abutments/Hour Wk 2 | Daily Error Rate | 90-day Complaints | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | Albert, Michael | White | 2011-08-01 | None | N/A - still employed | Active | Production | Production Manager | $54.50 | Elisa Bramante | Fully Meets | 0 | 0 | 0 | 0 |
| 1 | 2 | Bozzi, Charles | Asian | 2013-09-30 | 2014-08-07 | retiring | Voluntarily Terminated | Production | Production Manager | $50.50 | Elisa Bramante | Fully Meets | 0 | 0 | 0 | 0 |
| 2 | 3 | Butler, Webster L | White | 2016-01-28 | None | N/A - still employed | Active | Production | Production Manager | $55.00 | Elisa Bramante | Exceeds | 0 | 0 | 0 | 0 |
| 3 | 4 | Dunn, Amy | White | 2014-09-18 | None | N/A - still employed | Active | Production | Production Manager | $51.00 | Elisa Bramante | Fully Meets | 0 | 0 | 0 | 0 |
| 4 | 5 | Gray, Elijiah | White | 2015-06-02 | None | N/A - still employed | Active | Production | Production Manager | $54.00 | Elisa Bramante | Fully Meets | 0 | 0 | 0 | 0 |
Название: id Тип: INTEGER Название: Employee Name Тип: VARCHAR(50) Название: Race Desc Тип: VARCHAR(50) Название: Date of Hire Тип: DATE Название: TermDate Тип: DATE Название: Reason for Term Тип: VARCHAR(50) Название: Employment Status Тип: VARCHAR(50) Название: Department Тип: VARCHAR(50) Название: Position Тип: VARCHAR(50) Название: Pay Тип: MONEY Название: Manager Name Тип: VARCHAR(50) Название: Performance Score Тип: VARCHAR(50) Название: Abutments/Hour Wk 1 Тип: INTEGER Название: Abutments/Hour Wk 2 Тип: INTEGER Название: Daily Error Rate Тип: INTEGER Название: 90-day Complaints Тип: INTEGER ___________________________________ Таблица recruiting_costs: Кол-во рядов 22
| id | Employment Source | January | February | March | April | May | June | July | August | September | October | November | December | Total | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | Billboard | 520 | 520 | 520 | 520 | 0 | 0 | 612 | 612 | 729 | 749 | 910 | 500 | 6192 |
| 1 | 2 | Careerbuilder | 410 | 410 | 410 | 820 | 820 | 410 | 410 | 820 | 820 | 1230 | 820 | 410 | 7790 |
| 2 | 3 | Company Intranet - Partner | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 4 | Diversity Job Fair | 0 | 5129 | 0 | 0 | 0 | 0 | 0 | 4892 | 0 | 0 | 0 | 0 | 10021 |
| 4 | 5 | Employee Referral | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Название: id Тип: INTEGER Название: Employment Source Тип: VARCHAR(50) Название: January Тип: INTEGER Название: February Тип: INTEGER Название: March Тип: INTEGER Название: April Тип: INTEGER Название: May Тип: INTEGER Название: June Тип: INTEGER Название: July Тип: INTEGER Название: August Тип: INTEGER Название: September Тип: INTEGER Название: October Тип: INTEGER Название: November Тип: INTEGER Название: December Тип: INTEGER Название: Total Тип: INTEGER ___________________________________ Таблица salary_grid: Кол-во рядов 12
| id | Position | Salary Min | Salary Mid | Salary Max | Hourly Min | Hourly Mid | Hourly Max | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | Administrative Assistant | 30000 | 40000 | 50000 | 14.42 | 19.23 | 24.04 |
| 1 | 2 | Sr. Administrative Assistant | 35000 | 45000 | 55000 | 16.83 | 21.63 | 26.44 |
| 2 | 3 | Accountant I | 42274 | 51425 | 62299 | 20.32 | 24.72 | 29.95 |
| 3 | 4 | Accountant II | 50490 | 62158 | 74658 | 24.27 | 29.88 | 35.89 |
| 4 | 5 | Sr. Accountant | 63264 | 76988 | 92454 | 30.42 | 37.01 | 44.45 |
Название: id Тип: INTEGER Название: Position Тип: VARCHAR(50) Название: Salary Min Тип: INTEGER Название: Salary Mid Тип: INTEGER Название: Salary Max Тип: INTEGER Название: Hourly Min Тип: REAL Название: Hourly Mid Тип: REAL Название: Hourly Max Тип: REAL
Вывод 0.
В датасете присутствует 4 таблицы:
# создадим датафрейм с интересующей на синформацией - полом, группой возраста, расой, штатом, семейным положением сотрудников
df_dep_profile = pd.read_sql('''SELECT department,
id,
age,
CASE WHEN age <= 17 THEN '17-'
WHEN age BETWEEN 18 AND 24 THEN '18-24'
WHEN age BETWEEN 25 AND 29 THEN '25-29'
WHEN age BETWEEN 30 AND 34 THEN '30-34'
WHEN age BETWEEN 35 AND 39 THEN '35-39'
WHEN age BETWEEN 40 AND 44 THEN '40-44'
WHEN age BETWEEN 45 AND 49 THEN '45-49'
WHEN age BETWEEN 50 AND 54 THEN '50-54'
WHEN age BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group,
sex,
racedesc,
maritaldesc,
state
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL
ORDER BY department, id''', connect)
df_dep_profile
| department | id | age | age_group | sex | racedesc | maritaldesc | state | |
|---|---|---|---|---|---|---|---|---|
| 0 | Admin Offices | 1 | 30 | 30-34 | Female | Black or African American | Married | MA |
| 1 | Admin Offices | 2 | 34 | 30-34 | Male | Black or African American | Divorced | MA |
| 2 | Admin Offices | 3 | 31 | 30-34 | Male | White | Single | MA |
| 3 | Admin Offices | 5 | 30 | 30-34 | Female | White | Single | MA |
| 4 | Admin Offices | 7 | 33 | 30-34 | Male | White | Married | MA |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 202 | Software Engineering | 294 | 38 | 35-39 | Female | Black or African American | Single | MA |
| 203 | Software Engineering | 296 | 30 | 30-34 | Female | Asian | Single | MA |
| 204 | Software Engineering | 298 | 31 | 30-34 | Female | White | Married | MA |
| 205 | Software Engineering | 299 | 35 | 35-39 | Male | White | Single | MA |
| 206 | Software Engineering | 301 | 51 | 50-54 | Male | White | Single | MA |
207 rows × 8 columns
# визуализируем гендерный состав департаментов
fig, ax = plt.subplots(figsize=(12, 6))
sns.histplot(df_dep_profile, x='department', hue='sex', multiple='stack', palette='magma_r', shrink=0.7, linewidth=0)
for container in ax.containers:
for rect in container:
height = rect.get_height()
x = rect.get_x() - 0.15
y = rect.get_y() + height - 1
ax.text(x, y, '' if height == 0 else f'{height:.0f}', ha='left', va='bottom')
ax.set_title('Количество мужчин и женщин')
fig.text(y=0.92, x=0.125, s='в каждом департаменте', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Количество сотрудников')
upper_line(ax)
plt.show()
Всего в компании 6 департаментов: Admin Offices, Executive Office, IT/IS, Production, Sales, Software Engineering, общей численностью 207 человек. Наиболее многочисленный департамент в компании - Production, в нем на текущий момент числится 125 сотрудников. По компании в целом и в каждом отделе, кроме Sales и IT/IS, число сотрудников женского пола несколько больше, чем мужского.
# визуализируем гендерный и возрастной состав компании
fig, axes = plt.subplots(nrows=2, ncols=3, figsize=(17, 12), sharex=True)
departments = list(df_dep_profile.department.unique())
plt.suptitle('Количество сотрудников', y=1, x=0.245, fontsize=20)
fig.text(y=0.95, x=0.126, s='в каждом департаменте различных возрастных групп и пола', fontsize=16)
magma_palette = sns.color_palette('magma', n_colors=8)
order = sorted(df_dep_profile.age_group.unique(), reverse=True)
m_palette={i: j for i, j in zip(order, magma_palette)}
for i in range(2):
for j in range(3):
ax = axes[i][j]
department = departments.pop()
data = df_dep_profile[df_dep_profile['department'] == department]
sns.histplot(data, x='sex', hue='age_group', multiple="stack", hue_order=order, palette=m_palette, ax=ax, legend=((i == 1) & (j == 2)))
ax.set_title(f'Деп-т: {department}')
ax.set(xlabel='', ylabel='')
maximum = max(data[data['sex'] == 'Female']['sex'].count(), data[data['sex'] == 'Male']['sex'].count())
step = int(np.ceil((maximum) / 10))
ax.set_yticks(list(range(0, maximum + step, step)))
sns.despine()
if j == 0:
ax.set_ylabel('Кол-во сотрудников')
if i == 1:
ax.set_xticks(['Female', 'Male'], ['Женщины', 'Мужчины'])
if (i == 1) & (j == 2):
sns.move_legend(ax, "upper left", bbox_to_anchor=(1, 1.4), title='Возраст')
upper_line(ax, is_suptitle=True)
plt.show()
В целом по компании можно отметить преобладание возрастной группы с 25 до 35 лет, с присутствием некоторых различий по департаментам:
# визуализируем расовый состав компании
fig, ax = plt.subplots()
departments = sorted(df_dep_profile['department'].unique())
sns.histplot(df_dep_profile, x='department', hue='racedesc', multiple='dodge', palette='magma', shrink=0.6, linewidth=0)
for container in ax.containers:
for department, rect in zip(departments, container):
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
percent = height / df_dep_profile[df_dep_profile['department'] == department]['department'].count()
ax.text(x, y, '' if height == 0 else f'{percent: .0%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Расовый состав')
fig.text(y=0.92, x=0.125, s='сотрудников в каждом департаменте, % от всех сотрудников департамента', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Количество сотрудников')
upper_line(ax)
plt.show()
В каждом департаменте преобладают сотрудники белой расы - около 60%, однако везде (за исключением Executive Office) присутствуют и люди других рас, поэтому говорить о серьезных перекосах или отсутствии расового разнообразия скорее не приходится.
# визуализируем состав компании по семейному положению
fig, ax = plt.subplots()
departments = sorted(df_dep_profile['department'].unique())
sns.histplot(df_dep_profile, x='department', hue='maritaldesc', multiple='dodge', palette='magma_r', shrink=0.6, linewidth=0, hue_order=['Single', 'Married', 'Divorced', 'Separated', 'Widowed'])
for container in ax.containers:
for department, rect in zip(departments, container):
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
percent = height / df_dep_profile[df_dep_profile['department'] == department]['department'].count()
ax.text(x, y, '' if height == 0 else f'{percent: .0%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Семейное положение')
fig.text(y=0.92, x=0.125, s='сотрудников в каждом департаменте, % от всех сотрудников департамента', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Количество сотрудников')
sns.move_legend(ax, loc='best', title='')
upper_line(ax)
plt.show()
Большая часть сотрудников приходится на 2 группы людей: 101 человек незамужем/неженаты, 77 - замужем/женаты. В департаментах Production, Sales и Software Engineering преобладают незамужние/неженатые сотрудники, а в остальных - замужние/женатые.
# выделим в отдельный датафрейм краткую информацию о сотрудниках в табличном виде
df_dep_profile_stats = pd.read_sql('''(SELECT department,
percentile_cont(0.5) within group (order by age) as median_age,
round((sum(CASE WHEN sex = 'Male' THEN 1 ELSE 0 END)::numeric /count(sex) * 100), 2) as percent_of_male_gender,
count(sex) as total_employees,
round((sum(CASE WHEN racedesc = 'White' THEN 1 ELSE 0 END)::numeric /count(racedesc) * 100), 2) as percent_of_white_race,
string_agg(distinct state, ',') as states,
count(distinct state) as states_count
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL
GROUP BY department)
UNION
(SELECT 'Whole Company' as department,
percentile_cont(0.5) within group (order by age) as median_age,
round((sum(CASE WHEN sex = 'Male' THEN 1 ELSE 0 END)::numeric /count(sex) * 100), 2) as percent_of_male_gender,
count(sex) as total_employees,
round((sum(CASE WHEN racedesc = 'White' THEN 1 ELSE 0 END)::numeric /count(racedesc) * 100), 2) as percent_of_white_race,
string_agg(distinct state, ',') as states,
count(distinct state) as states_count
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL)
ORDER BY department''', connect)
df_dep_profile_stats
| department | median_age | percent_of_male_gender | total_employees | percent_of_white_race | states | states_count | |
|---|---|---|---|---|---|---|---|
| 0 | Admin Offices | 31.0 | 42.86 | 7 | 57.14 | MA | 1 |
| 1 | Executive Office | 63.0 | 0.00 | 1 | 100.00 | MA | 1 |
| 2 | IT/IS | 36.0 | 52.50 | 40 | 60.00 | CT,MA,TX | 3 |
| 3 | Production | 38.0 | 39.20 | 125 | 63.20 | MA | 1 |
| 4 | Sales | 33.0 | 55.56 | 27 | 48.15 | AL,AZ,CA,CO,CT,FL,GA,ID,IN,KY,MA,ME,MT,NC,ND,N... | 24 |
| 5 | Software Engineering | 35.0 | 28.57 | 7 | 71.43 | MA | 1 |
| 6 | Whole Company | 37.0 | 43.48 | 207 | 60.87 | AL,AZ,CA,CO,CT,FL,GA,ID,IN,KY,MA,ME,MT,NC,ND,N... | 24 |
Медианный возраст большинства департаментов находится в диапазоне от 31 до 38 лет. Большая часть компании базируется в штате Массачусетс, однако департамент Sales представлен в еще 23 штатах.
# выделим в датафрейм минимальную, 25-процентиль, медианную, 75-процентиль, максимальную оплату труда по отделам
df_pay_stats = pd.read_sql('''(SELECT department,
min("Pay Rate") as minimal_pay,
percentile_cont(0.25) within group (order by "Pay Rate") as "25_percentile",
percentile_cont(0.5) within group (order by "Pay Rate") as "median_pay",
percentile_cont(0.75) within group (order by "Pay Rate") as "75_percentile",
percentile_cont(0.99) within group (order by "Pay Rate") as "99_percentile",
max("Pay Rate") as maximal_pay
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL
GROUP BY department)
UNION
(SELECT 'Whole Company' AS department,
min("Pay Rate") as minim_pay,
percentile_cont(0.25) within group (order by "Pay Rate") as "25_percentile",
percentile_cont(0.5) within group (order by "Pay Rate") as "median_pay",
percentile_cont(0.75) within group (order by "Pay Rate") as "75_percentile",
percentile_cont(0.99) within group (order by "Pay Rate") as "99_percentile",
max("Pay Rate") as maximal_pay
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL)
ORDER BY department
''', connect)
df_pay_stats
| department | minimal_pay | 25_percentile | median_pay | 75_percentile | 99_percentile | maximal_pay | |
|---|---|---|---|---|---|---|---|
| 0 | Admin Offices | 16.56 | 25.750000 | 29.00 | 34.950001 | 53.797000 | 55.00 |
| 1 | Executive Office | 80.00 | 80.000000 | 80.00 | 80.000000 | 80.000000 | 80.00 |
| 2 | IT/IS | 26.00 | 38.500000 | 45.00 | 54.324999 | 65.000000 | 65.00 |
| 3 | Production | 14.00 | 18.000000 | 22.00 | 24.250000 | 55.000000 | 60.00 |
| 4 | Sales | 54.00 | 55.000000 | 55.00 | 55.750000 | 59.220000 | 60.00 |
| 5 | Software Engineering | 27.00 | 47.799999 | 49.25 | 55.754999 | 57.052799 | 57.12 |
| 6 | Whole Company | 14.00 | 20.000000 | 26.00 | 53.000000 | 64.940000 | 80.00 |
# отобразим найденные показатели на графике
fig, ax = plt.subplots()
sns.scatterplot(df_pay_stats[df_pay_stats['department'] != 'Whole Company'].melt(['department']), x='department', y='value', hue='variable', palette='magma', s=150, ax=ax)
ax.yaxis.set_minor_locator(plt.MultipleLocator(5))
ax.set_title('Статистика по заработной плате')
fig.text(y=0.92, x=0.125, s='в каждом департаменте в сравнении с показателями по компании', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Заработная плата')
c_palette = sns.color_palette('magma', n_colors=6)
for i, stat in enumerate(list(*(df_pay_stats[df_pay_stats['department'] == 'Whole Company'].iloc[:, 1:]).values.tolist())):
ax.axhline(stat, color=c_palette[i], linewidth=0.7, linestyle='--')
sns.move_legend(ax, loc="upper left", bbox_to_anchor=(1, 1), title='', markerscale=2, reverse=True)
upper_line(ax)
plt.show()
Ожидаемо, наиболее высокая заработная плата у CEO, Executive Office. Не учитывая этот единичный случай, можно сказать, что наиболее высокая медианная заработная плата - у сотрудников отдела продаж. При этом по данному департаменту распределение заработных плат довольно узкое, поэтому можно сделать вывод об их сбалансированности. Наиболее низкая по компании медиана заработной платы у сотрудников департамента Production, с большим разбросом до максимальной, однако данные выбросы оправданы в виду разлчных должностей сотрудников - максимум ожидаемо приходится на Director of Operations.
# рассчитаем бюджеты подразделений
df_budgets = pd.read_sql('''SELECT department,
sum("Pay Rate") * 40 * 4 as total_budget, count(id) as people_count
FROM hr_dataset
WHERE hr_dataset."Date of Termination" is NULL
GROUP BY department
ORDER BY total_budget DESC
''', connect)
# визуализируем полученные суммы
fig, ax = plt.subplots()
sns.barplot(df_budgets, y='department', x='total_budget', palette='magma', zorder=4)
ax.set_title('Бюджеты подразделений')
fig.text(y=0.92, x=0.125, s='за месяц исходя из текущего числа сотрудников и 40-часовой рабочей недели', fontsize=12)
ax.set_xlabel('Бюджет, долларов', loc='left')
ax.set_ylabel('')
ax.xaxis.set_minor_locator(plt.MultipleLocator(10000))
ax.set_yticks(ticks=ax.get_yticks(), labels=['Production', 'IT/IS', 'Sales', 'Software\nEngineering', 'Admin\nOffices', 'Executive\nOffice'])
ax.set_xticks(ticks=[i for i in range(0, 500000, 50000)], labels=['{0:,}'.format(i) for i in range(0, 500000, 50000)])
ax.grid(axis='x', zorder=1)
upper_line(ax)
plt.show()
Наибольший бюджет имеет подразделение production (125 человек), что оправдано наибольшим числом сотрудников. За ним следует бюджет IT/IS (40 человек) - стоит обратить внимание, что количество сотрудников там более чем в 2 раза меньше кол-ва сотрудников в Production, однако бюджет составляет около 2/3 бюджета Production. Скорее всего, данное наблюдение объясняется более высококвалифицированной рабочей силой отдела IT/IS.
# подсчитаем, сколько уникальных должностей существует в hr_dataset и production_staff
df_positions_hr_prod = pd.read_sql('''WITH all_positions AS ((SELECT
distinct "position" as positions
FROM hr_dataset
WHERE "Date of Termination" is NULL)
UNION
(SELECT
DISTINCT "Position" as positions
FROM production_staff))
SELECT count(positions)
FROM all_positions
''', connect)
df_positions_hr_prod
| count | |
|---|---|
| 0 | 28 |
В объединенной из таблиц hr_dataset и productyion_staff таблице 28 должностей, как и в таблице hr_dataset, значит, все должности обеих таблиц имеются в таблице hr_dataset. Ранее мы узнали, что в salary_grid только 12 должностей, 8 из которых присутствуют в таблице hr_dataset. 4 должности, которые отсутствуют в hr_dataset, но имеются в salary_grid не дадут нам дополнительной информации для анализа, так как они не привязаны ни к департаментам, ни к характеристикам сотрудников. Остальные же должности уже есть в hr_dataset, поэтому мы можем рассчитать собственные статистические значения по заданным условиям, без использования таблицы salary_grid.
# рассчитаем базовые метрики и разброс от минимального до максимального значения
df_positions = pd.read_sql('''SELECT
department, position, count(id) as personnel_count,
min("Pay Rate") as minimal_pay,
percentile_cont(0.25) within group (order by "Pay Rate") as "25_percentile",
percentile_cont(0.5) within group (order by "Pay Rate") as "median_pay",
percentile_cont(0.75) within group (order by "Pay Rate") as "75_percentile",
percentile_cont(0.99) within group (order by "Pay Rate") as "99_percentile",
max("Pay Rate") as maximal_pay,
round((max("Pay Rate") - min("Pay Rate")) / min("Pay Rate") * 100) as "spread, per cent"
FROM hr_dataset
WHERE "Date of Termination" is NULL
group by department, position
order by "spread, per cent" desc
''', connect)
df_positions
| department | position | personnel_count | minimal_pay | 25_percentile | median_pay | 75_percentile | 99_percentile | maximal_pay | spread, per cent | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | IT/IS | Network Engineer | 8 | 27.00 | 38.500000 | 42.500000 | 45.500000 | 48.952999 | 49.10 | 82.0 |
| 1 | Production | Production Technician I | 84 | 14.00 | 16.562500 | 19.875000 | 22.000000 | 24.542500 | 24.75 | 77.0 |
| 2 | IT/IS | Database Administrator | 8 | 30.20 | 33.350000 | 37.525000 | 40.550000 | 42.711500 | 42.75 | 42.0 |
| 3 | Production | Production Technician II | 31 | 22.00 | 24.000000 | 25.000000 | 26.695000 | 29.000000 | 29.00 | 32.0 |
| 4 | Admin Offices | Accountant I | 3 | 23.00 | 25.750000 | 28.500000 | 28.750000 | 28.990000 | 29.00 | 26.0 |
| 5 | IT/IS | IT Support | 4 | 26.00 | 27.117500 | 28.240000 | 29.592500 | 31.327700 | 31.40 | 21.0 |
| 6 | Software Engineering | Software Engineer | 6 | 47.60 | 48.312500 | 52.379999 | 55.877500 | 57.063999 | 57.12 | 20.0 |
| 7 | Production | Production Manager | 9 | 51.00 | 53.000000 | 54.000000 | 55.000000 | 55.000000 | 55.00 | 8.0 |
| 8 | IT/IS | Sr. Network Engineer | 5 | 53.00 | 53.799999 | 54.099998 | 55.200001 | 56.160001 | 56.20 | 6.0 |
| 9 | Sales | Area Sales Manager | 24 | 54.00 | 55.000000 | 55.000000 | 55.500000 | 56.770000 | 57.00 | 6.0 |
| 10 | IT/IS | Senior BI Developer | 3 | 50.25 | 50.625000 | 51.000000 | 51.625000 | 52.225000 | 52.25 | 4.0 |
| 11 | Sales | Sales Manager | 2 | 54.00 | 54.500000 | 55.000000 | 55.500000 | 55.980000 | 56.00 | 4.0 |
| 12 | IT/IS | BI Developer | 4 | 45.00 | 45.000000 | 45.000000 | 45.250000 | 45.970000 | 46.00 | 2.0 |
| 13 | Executive Office | President & CEO | 1 | 80.00 | 80.000000 | 80.000000 | 80.000000 | 80.000000 | 80.00 | 0.0 |
| 14 | Admin Offices | Sr. Accountant | 2 | 34.95 | 34.950001 | 34.950001 | 34.950001 | 34.950001 | 34.95 | 0.0 |
| 15 | IT/IS | Sr. DBA | 1 | 61.30 | 61.299999 | 61.299999 | 61.299999 | 61.299999 | 61.30 | 0.0 |
| 16 | Production | Director of Operations | 1 | 60.00 | 60.000000 | 60.000000 | 60.000000 | 60.000000 | 60.00 | 0.0 |
| 17 | Admin Offices | Shared Services Manager | 1 | 55.00 | 55.000000 | 55.000000 | 55.000000 | 55.000000 | 55.00 | 0.0 |
| 18 | Admin Offices | Administrative Assistant | 1 | 16.56 | 16.559999 | 16.559999 | 16.559999 | 16.559999 | 16.56 | 0.0 |
| 19 | Sales | Director of Sales | 1 | 60.00 | 60.000000 | 60.000000 | 60.000000 | 60.000000 | 60.00 | 0.0 |
| 20 | IT/IS | BI Director | 1 | 63.50 | 63.500000 | 63.500000 | 63.500000 | 63.500000 | 63.50 | 0.0 |
| 21 | Software Engineering | Software Engineering Manager | 1 | 27.00 | 27.000000 | 27.000000 | 27.000000 | 27.000000 | 27.00 | 0.0 |
| 22 | IT/IS | CIO | 1 | 65.00 | 65.000000 | 65.000000 | 65.000000 | 65.000000 | 65.00 | 0.0 |
| 23 | IT/IS | Data Architect | 1 | 55.00 | 55.000000 | 55.000000 | 55.000000 | 55.000000 | 55.00 | 0.0 |
| 24 | IT/IS | IT Director | 1 | 65.00 | 65.000000 | 65.000000 | 65.000000 | 65.000000 | 65.00 | 0.0 |
| 25 | IT/IS | IT Manager - DB | 1 | 62.00 | 62.000000 | 62.000000 | 62.000000 | 62.000000 | 62.00 | 0.0 |
| 26 | IT/IS | IT Manager - Infra | 1 | 63.00 | 63.000000 | 63.000000 | 63.000000 | 63.000000 | 63.00 | 0.0 |
| 27 | IT/IS | IT Manager - Support | 1 | 64.00 | 64.000000 | 64.000000 | 64.000000 | 64.000000 | 64.00 | 0.0 |
Наибольшй разброс заработных плат наблюдается на должностях Network Engineer и Production Technician I. Следует обратить внимание на эти должности и определить, оправдано ли получение сотрудниками настолько отличных вознаграждений.
# выделим в датафрейм необходимую информацию: отдел, пол, расу, семейное положение и оплату труда
df_pay = pd.read_sql('''SELECT department, sex, racedesc, age, "Pay Rate", maritaldesc
FROM hr_dataset
WHERE "Date of Termination" is NULL
''', connect)
display(df_pay)
# рассчитаем медианы заработной платы в зависимости от пола
medians_sex = pd.read_sql('''select sex, percentile_cont(0.5) within group (order by "Pay Rate") as "median_pay"
from hr_dataset
group by sex
''', connect)
display(medians_sex)
| department | sex | racedesc | age | Pay Rate | maritaldesc | |
|---|---|---|---|---|---|---|
| 0 | Admin Offices | Female | Black or African American | 30 | 28.50 | Married |
| 1 | Admin Offices | Male | Black or African American | 34 | 23.00 | Divorced |
| 2 | Admin Offices | Male | White | 31 | 29.00 | Single |
| 3 | Admin Offices | Female | White | 30 | 16.56 | Single |
| 4 | Admin Offices | Male | White | 33 | 55.00 | Married |
| ... | ... | ... | ... | ... | ... | ... |
| 202 | IT/IS | Male | Asian | 48 | 52.25 | Single |
| 203 | IT/IS | Male | Asian | 36 | 51.00 | Single |
| 204 | IT/IS | Male | White | 34 | 46.00 | Single |
| 205 | IT/IS | Male | White | 30 | 45.00 | Married |
| 206 | IT/IS | Male | Black or African American | 28 | 45.00 | Married |
207 rows × 6 columns
| sex | median_pay | |
|---|---|---|
| 0 | Female | 24.0 |
| 1 | Male | 26.0 |
# визуализируем зависимость заработной платы от пола и возраста сотрудников
g = sns.jointplot(df_pay, x='age', y='Pay Rate', hue='sex', palette=sns.color_palette('magma_r', n_colors=2))
g.ax_joint.axhline(medians_sex.iloc[1, 1], linestyle='--', color=sns.color_palette('magma')[1])
g.ax_joint.axhline(medians_sex.iloc[0, 1], linestyle='--', color=sns.color_palette('magma')[4])
plt.text(58, medians_sex.iloc[1, 1] + 2, f"Мед. мужчин: {medians_sex.iloc[1, 1]}$ / час", fontsize=8)
plt.text(58, medians_sex.iloc[0, 1] - 3, f"Мед. женщин: {medians_sex.iloc[0, 1]}$ / час", fontsize=8)
g.fig.suptitle('Зависимость заработной платы', x=0.12, y=1.1, ha='left')
plt.text(11, 135, 'от пола и возраста сотрудников', fontsize=12)
plt.xlabel('Возраст', loc='left')
plt.ylabel('Ставка, $ в час')
g.fig.axes[0].plot([0.00, 0.9], [1.11, 1.11], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
g.fig.axes[0].plot([0.00, 0.9], [1, 1], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
plt.show()
Медианная заработная плата женщин несколько ниже, чем у мужчин, однако корреляции между возрастом и оплатой труда не наблюдается.
# визуализируем зависимость заработной платы от пола и расы сотрудников
fig, ax = plt.subplots()
sns.scatterplot(df_pay, y='racedesc', x='Pay Rate', hue='sex', palette='magma_r', edgecolor='black', zorder=4)
sns.boxplot(df_pay, y='racedesc', x='Pay Rate', palette=sns.color_palette('magma', n_colors=8)[2:], zorder=3)
ax.set_title('Зависимость заработной платы')
fig.text(y=0.92, x=0.125, s='от расы и пола сотрудников', fontsize=12)
ax.set_xlabel('Заработная плата, $/час')
ax.set_ylabel('Раса')
ax.set_yticklabels(['Black', 'White', 'Asian', 'Hispanic', 'Two or more', 'American-Indian/\nor Alaska Native'])
upper_line(ax)
plt.show()
Наблюдается, что для черной расы медианная заработная плата больше, чем для большинства других. Однако данное отличие незначительно и не доказывает наличие корреляции. Резкий выброс для группы two or more наблюдается из-за недостаточного количества данных в выборке - человек в этой группе всего трое.
# выделим в отдельный датафрейм необходимые данные
df_experience = pd.read_sql('''
SELECT department, id, "Performance Score", perf_scoreid, dob, racedesc, "Days Employed", maritaldesc, sex,
DATE_PART('year', AGE("Date of Hire", dob)) AS hire_age,
CASE WHEN DATE_PART('year', AGE("Date of Hire", dob)) <= 17 THEN '17-'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 18 AND 24 THEN '18-24'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 25 AND 29 THEN '25-29'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 30 AND 34 THEN '30-34'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 35 AND 39 THEN '35-39'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 40 AND 44 THEN '40-44'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 45 AND 49 THEN '45-49'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 50 AND 54 THEN '50-54'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset
WHERE "Date of Termination" is not NULL
ORDER BY perf_scoreid
''',
connect)
df_experience
| department | id | Performance Score | perf_scoreid | dob | racedesc | Days Employed | maritaldesc | sex | hire_age | age_group | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Production | 135 | 90-day meets | 0 | 1973-12-08 | White | 1347 | Married | Female | 37.0 | 35-39 |
| 1 | IT/IS | 23 | 90-day meets | 0 | 1987-12-17 | Black or African American | 299 | Divorced | Male | 27.0 | 25-29 |
| 2 | Production | 141 | 90-day meets | 0 | 1981-10-18 | White | 164 | Single | Male | 29.0 | 25-29 |
| 3 | Production | 204 | 90-day meets | 0 | 1988-09-19 | White | 447 | Married | Female | 22.0 | 18-24 |
| 4 | Production | 199 | 90-day meets | 0 | 1987-05-24 | White | 125 | Single | Male | 23.0 | 18-24 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 98 | Production | 167 | N/A- too early to review | 9 | 1984-10-15 | Asian | 19 | Divorced | Female | 26.0 | 25-29 |
| 99 | Production | 145 | N/A- too early to review | 9 | 1972-11-09 | White | 2 | Widowed | Male | 39.0 | 35-39 |
| 100 | Production | 234 | N/A- too early to review | 9 | 1976-12-26 | White | 69 | Widowed | Female | 36.0 | 35-39 |
| 101 | Production | 237 | N/A- too early to review | 9 | 1986-12-10 | Two or more races | 45 | Married | Female | 24.0 | 18-24 |
| 102 | Admin Offices | 4 | N/A- too early to review | 9 | 1985-09-16 | White | 58 | Married | Female | 29.0 | 25-29 |
103 rows × 11 columns
# визуализируем зависимость между сроком работы в компании, полом и возрастом найма сотрудника
fig, ax = plt.subplots()
sns.boxplot(df_experience.sort_values(by='age_group'), y='Days Employed', hue='age_group', palette='magma_r', x='sex')
ax.set_title('Зависимость стажа в компании')
fig.text(y=0.92, x=0.125, s='от пола и возраста найма', fontsize=12)
ax.set_ylabel('Стаж в компании, дней')
ax.set_xlabel('')
upper_line(ax)
plt.show()
Среди мужчин заметен явный тренд к более продолжительной работе в компании для нанятых в более старшем возрасте - от 40 и далее. Это может быть обосновано тем, что старшие люди склонны к большей стабильности. Для женщин, однако, такого сказать нельзя, заметно лишь некоторое повышение срока работы для возраста найма 30-39 лет.
# визуализируем зависимость между сроком работы в компании, полом и семейным положением сотрудника
fig, ax = plt.subplots()
sns.boxplot(df_experience, y='Days Employed', hue='maritaldesc', palette='magma_r', x='sex')
ax.set_title('Зависимость стажа в компании')
fig.text(y=0.92, x=0.125, s='от пола и семейного положения', fontsize=12)
ax.set_ylabel('Стаж в компании, дней')
ax.set_xlabel('')
upper_line(ax)
plt.show()
N.B. Предлагается обращать внимание только на группы single и married, так как основная часть людей сосредоточены именно в этих группах, в то время как другие группы слишком малы, чтобы делать обоснованные выводы.
Медианное значение стажа в компании для незамужних женщин в 2 раза выше, чем для замужних. Аналогично и для мужчин, однако данное явление не так ярко выражено.
# визуализируем зависимость между сроком работы в компании и расой сотрудника
fig, ax = plt.subplots()
sns.boxplot(df_experience, y='Days Employed', palette='magma_r', x='racedesc')
ax.set_title('Зависимость стажа в компании')
fig.text(y=0.92, x=0.125, s='от расы сотрудника', fontsize=12)
ax.set_ylabel('Стаж в компании, дней')
ax.set_xlabel('')
upper_line(ax)
plt.show()
N.B. Предлагается обращать внимание только на расы white, black и asian, так как основная часть людей сосредоточены именно в этих группах, в то время как другие группы слишком малы, чтобы делать обоснованные выводы.
Выраженной зависимости стажа от расы сотрудника не наблюдается.
weighted_per_cent_from_perf = процент людей определенного пола, получивших тот или иной рейтинг, от их гендерной группы / (сумма процентов людей определенного пола, получивших ту или иную оценку, от их гендерной группы)
Процент людей определенного пола, получивших определенную оценку, от всех людей в гендерной группе - по смыслу представляет собой число: сколько было бы людей, получивших такой рейтинг, если бы в гендерной группе было ровно 100 человек. Поэтому для определения weighted_per_cent_from_perf мы в знаменателе суммируем именно эти показатели - это позволяет нам привести гендерные группы к сравнимому состоянию, представляя, что в каждой из них ровно сто человек.
Чем больше значение показателя weighted_per_cent_from_perf, тем больше людей определенного пола по сравнению с другим полом получили именно такую оценку перформанса.
Наличие двух показателей - per_cent_from_sex и weighted_per_cent_from_perf - позволит нам проанализировать оценки перформанса и из зависимость от пола в двух разрезах: преимущественные оценки по гендерной группе и преимущественная гендерная группа по оценке.
N.B. далее для расовых, возрастных и других групп будет использован аналогичный подход.
# рассчитаем указанные показатели
df_perf_sex = pd.read_sql(''' with data_1 AS (SELECT sex, "Performance Score" as perf, perf_scoreid, count(*) as sex_score_count
FROM hr_dataset
GROUP BY sex, "Performance Score", perf_scoreid),
data_2 AS (SELECT *, sum(sex_score_count) over (partition by sex)::int as sex_count,
round(sex_score_count / sum(sex_score_count) over (partition by sex) * 100, 2) as per_cent_from_sex
FROM data_1)
SELECT *, round(per_cent_from_sex / sum(per_cent_from_sex) over (partition by perf) * 100, 2) as weighted_per_cent_from_perf
FROM data_2
ORDER BY perf_scoreid
''', connect)
df_perf_sex
| sex | perf | perf_scoreid | sex_score_count | sex_count | per_cent_from_sex | weighted_per_cent_from_perf | |
|---|---|---|---|---|---|---|---|
| 0 | Female | 90-day meets | 0 | 19 | 177 | 10.73 | 54.33 |
| 1 | Male | 90-day meets | 0 | 12 | 133 | 9.02 | 45.67 |
| 2 | Female | PIP | 1 | 5 | 177 | 2.82 | 48.37 |
| 3 | Male | PIP | 1 | 4 | 133 | 3.01 | 51.63 |
| 4 | Male | Needs Improvement | 2 | 10 | 133 | 7.52 | 72.73 |
| 5 | Female | Needs Improvement | 2 | 5 | 177 | 2.82 | 27.27 |
| 6 | Female | Fully Meets | 3 | 101 | 177 | 57.06 | 48.68 |
| 7 | Male | Fully Meets | 3 | 80 | 133 | 60.15 | 51.32 |
| 8 | Male | Exceeds | 4 | 12 | 133 | 9.02 | 49.94 |
| 9 | Female | Exceeds | 4 | 16 | 177 | 9.04 | 50.06 |
| 10 | Male | Exceptional | 5 | 4 | 133 | 3.01 | 51.63 |
| 11 | Female | Exceptional | 5 | 5 | 177 | 2.82 | 48.37 |
| 12 | Female | N/A- too early to review | 9 | 26 | 177 | 14.69 | 63.98 |
| 13 | Male | N/A- too early to review | 9 | 11 | 133 | 8.27 | 36.02 |
# на двух графиках изобразим оценку перформанса по гендерным группам
fig, ax = plt.subplots()
ax = sns.barplot(df_perf_sex, y='per_cent_from_sex', hue='perf', x='sex', palette='magma')
for container in ax.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
ax.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Зависимость оценки перформанса')
fig.text(y=0.92, x=0.125, s='от пола сотрудника (процент от гендерной группы)', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Процент гендерной группы, имеющих такую оценку')
upper_line(ax)
fig2, ax2 = plt.subplots()
sns.barplot(df_perf_sex, y='weighted_per_cent_from_perf', x='perf', hue='sex', palette='magma_r', ax=ax2)
for container in ax2.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax2.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax2.set_title('Зависимость оценки перформанса от пола сотрудника', y=1.04)
fig2.text(y=0.92, x=0.125, s='(% людей определенного пола от групп одинаковой оценки перформанса, \nвзвешенный по числу людей в гендерной группе)', fontsize=12)
ax2.set_xlabel('')
ax2.set_ylabel('% Процент гендерной группы, имеющих такую оценку')
ax2.plot([.03, 0.9], [0.96, 0.96], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
ax2.plot([.03, 0.9], [0.85, 0.85], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
plt.show()
# рассчитаем средние значения оценки перформанса
df_perf_sex_avg = pd.read_sql(''' SELECT sex, AVG(perf_scoreid)
FROM hr_dataset
WHERE perf_scoreid BETWEEN 1 AND 5
GROUP BY sex
''', connect)
df_perf_sex_avg
| sex | avg | |
|---|---|---|
| 0 | Female | 3.083333 |
| 1 | Male | 3.018182 |
Преобладающая оценка как для женщин, так и для мужчин "Fully Meets". Все оценки, за исключением "Needs Improvement" и "NA-too early to review" распределены равномерно: "Needs Improvement" получило значительно больше мужчин, а "NA-too early to review" - женщин. В целом, не учитывая оценки "90-day meets" и "NA-too early to review", средняя оценка женщин незначительно выше, чем у мужчин.
# рассчитаем относительные показатели по каждой оценке перформанса и каждой возрастной группе
df_perf_age = pd.read_sql(''' with data_1 as (
SELECT id, "Performance Score", perf_scoreid,
CASE WHEN DATE_PART('year', AGE("Date of Hire", dob)) <= 17 THEN '17-'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 18 AND 24 THEN '18-24'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 25 AND 29 THEN '25-29'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 30 AND 34 THEN '30-34'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 35 AND 39 THEN '35-39'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 40 AND 44 THEN '40-44'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 45 AND 49 THEN '45-49'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 50 AND 54 THEN '50-54'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset),
data_2 AS
(SELECT age_group, "Performance Score" as perf, perf_scoreid, count(*) as age_score_count
FROM data_1
GROUP BY age_group, "Performance Score", perf_scoreid),
data_3 AS
(SELECT *, sum(age_score_count) over (partition by age_group)::int as age_count,
round(age_score_count / sum(age_score_count) over (partition by age_group) * 100, 2) as per_cent_from_age_group
FROM data_2)
SELECT *, round(per_cent_from_age_group / sum(per_cent_from_age_group) over (partition by perf) * 100, 2) as weighted_per_cent_from_perf_group
FROM data_3
ORDER BY age_group, perf_scoreid
''', connect)
df_perf_age
| age_group | perf | perf_scoreid | age_score_count | age_count | per_cent_from_age_group | weighted_per_cent_from_perf_group | |
|---|---|---|---|---|---|---|---|
| 0 | 18-24 | 90-day meets | 0 | 3 | 43 | 6.98 | 8.66 |
| 1 | 18-24 | PIP | 1 | 2 | 43 | 4.65 | 18.59 |
| 2 | 18-24 | Needs Improvement | 2 | 3 | 43 | 6.98 | 27.96 |
| 3 | 18-24 | Fully Meets | 3 | 24 | 43 | 55.81 | 9.76 |
| 4 | 18-24 | Exceeds | 4 | 5 | 43 | 11.63 | 10.73 |
| 5 | 18-24 | N/A- too early to review | 9 | 6 | 43 | 13.95 | 19.00 |
| 6 | 25-29 | 90-day meets | 0 | 12 | 81 | 14.81 | 18.38 |
| 7 | 25-29 | PIP | 1 | 2 | 81 | 2.47 | 9.87 |
| 8 | 25-29 | Needs Improvement | 2 | 4 | 81 | 4.94 | 19.79 |
| 9 | 25-29 | Fully Meets | 3 | 46 | 81 | 56.79 | 9.93 |
| 10 | 25-29 | Exceeds | 4 | 4 | 81 | 4.94 | 4.56 |
| 11 | 25-29 | Exceptional | 5 | 2 | 81 | 2.47 | 15.62 |
| 12 | 25-29 | N/A- too early to review | 9 | 11 | 81 | 13.58 | 18.49 |
| 13 | 30-34 | 90-day meets | 0 | 5 | 67 | 7.46 | 9.26 |
| 14 | 30-34 | PIP | 1 | 1 | 67 | 1.49 | 5.96 |
| 15 | 30-34 | Needs Improvement | 2 | 6 | 67 | 8.96 | 35.90 |
| 16 | 30-34 | Fully Meets | 3 | 35 | 67 | 52.24 | 9.14 |
| 17 | 30-34 | Exceeds | 4 | 5 | 67 | 7.46 | 6.88 |
| 18 | 30-34 | Exceptional | 5 | 5 | 67 | 7.46 | 47.19 |
| 19 | 30-34 | N/A- too early to review | 9 | 10 | 67 | 14.93 | 20.33 |
| 20 | 35-39 | 90-day meets | 0 | 6 | 49 | 12.24 | 15.19 |
| 21 | 35-39 | Needs Improvement | 2 | 2 | 49 | 4.08 | 16.35 |
| 22 | 35-39 | Fully Meets | 3 | 30 | 49 | 61.22 | 10.71 |
| 23 | 35-39 | Exceeds | 4 | 5 | 49 | 10.20 | 9.41 |
| 24 | 35-39 | N/A- too early to review | 9 | 6 | 49 | 12.24 | 16.67 |
| 25 | 40-44 | 90-day meets | 0 | 3 | 34 | 8.82 | 10.95 |
| 26 | 40-44 | PIP | 1 | 2 | 34 | 5.88 | 23.50 |
| 27 | 40-44 | Fully Meets | 3 | 22 | 34 | 64.71 | 11.32 |
| 28 | 40-44 | Exceeds | 4 | 4 | 34 | 11.76 | 10.85 |
| 29 | 40-44 | Exceptional | 5 | 2 | 34 | 5.88 | 37.19 |
| 30 | 40-44 | N/A- too early to review | 9 | 1 | 34 | 2.94 | 4.00 |
| 31 | 45-49 | 90-day meets | 0 | 1 | 19 | 5.26 | 6.53 |
| 32 | 45-49 | PIP | 1 | 2 | 19 | 10.53 | 42.09 |
| 33 | 45-49 | Fully Meets | 3 | 12 | 19 | 63.16 | 11.05 |
| 34 | 45-49 | Exceeds | 4 | 1 | 19 | 5.26 | 4.85 |
| 35 | 45-49 | N/A- too early to review | 9 | 3 | 19 | 15.79 | 21.50 |
| 36 | 50-54 | Fully Meets | 3 | 3 | 7 | 42.86 | 7.50 |
| 37 | 50-54 | Exceeds | 4 | 4 | 7 | 57.14 | 52.72 |
| 38 | 55-59 | Fully Meets | 3 | 6 | 6 | 100.00 | 17.49 |
| 39 | 60+ | 90-day meets | 0 | 1 | 4 | 25.00 | 31.03 |
| 40 | 60+ | Fully Meets | 3 | 3 | 4 | 75.00 | 13.12 |
# на двух графиках изобразим оценку перформанса по возрастным группам
fig, ax = plt.subplots()
sns.barplot(df_perf_age, y='per_cent_from_age_group', hue='perf', x='age_group', palette='magma', ax=ax)
for container in ax.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Зависимость оценки перформанса от возраста сотрудника')
fig.text(y=0.92, x=0.125, s='(% людей с определенной оценкой перформанса от возрастной группы)', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Процент возрастной группы, имеющих такую оценку')
upper_line(ax)
fig2, ax2 = plt.subplots()
sns.barplot(df_perf_age, y='weighted_per_cent_from_perf_group', x='perf', hue='age_group', palette='magma', hue_order=df_perf_age['age_group'].sort_values().unique(), ax=ax2)
for container in ax2.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax2.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax2.set_title('Зависимость оценки перформанса от возраста сотрудника', y=1.04)
fig2.text(y=0.92, x=0.125, s='(% людей определенного возраста от групп одинаковой оценки перформанса, \nвзвешенный по числу людей в возрастной группе)', fontsize=12)
ax2.set_xlabel('')
ax2.set_ylabel('% Процент возрастной группы, имеющих такую оценку')
ax2.plot([.03, 0.9], [0.96, 0.96], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
ax2.plot([.03, 0.9], [0.85, 0.85], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
plt.show()
# рассчитаем средние значения оценки перформанса по возрастным группам
df_perf_age_avg = pd.read_sql(''' with data_1 as (
SELECT id, "Performance Score", perf_scoreid,
CASE WHEN DATE_PART('year', AGE("Date of Hire", dob)) <= 17 THEN '17-'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 18 AND 24 THEN '18-24'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 25 AND 29 THEN '25-29'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 30 AND 34 THEN '30-34'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 35 AND 39 THEN '35-39'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 40 AND 44 THEN '40-44'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 45 AND 49 THEN '45-49'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 50 AND 54 THEN '50-54'
WHEN DATE_PART('year', AGE("Date of Hire", dob)) BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset)
SELECT age_group, AVG(perf_scoreid)
FROM data_1
where perf_scoreid between 1 and 5
GROUP BY age_group
order by age_group
''', connect)
df_perf_age_avg
| age_group | avg | |
|---|---|---|
| 0 | 18-24 | 2.941176 |
| 1 | 25-29 | 3.000000 |
| 2 | 30-34 | 3.134615 |
| 3 | 35-39 | 3.081081 |
| 4 | 40-44 | 3.133333 |
| 5 | 45-49 | 2.800000 |
| 6 | 50-54 | 3.571429 |
| 7 | 55-59 | 3.000000 |
| 8 | 60+ | 3.000000 |
В каждой возрастной группе, за исключением группы 50-54, превалирующая оценка Fully Meets. Значительная часть тех, кто имеет Performance Improvement Plan, находится в возрасте 45-49, оценку Exceed преимущественно получают люди в диапазоне 50-54 года, а оценку Exceptional - в диапазоне 30-34 года.
# рассчитаем относительные показатели по каждой оценке перформанса и каждой расовой группе
df_perf_race = pd.read_sql(''' with data_1 AS (SELECT racedesc, "Performance Score" as perf, perf_scoreid, count(*) as race_score_count
FROM hr_dataset
GROUP BY racedesc, "Performance Score", perf_scoreid),
data_2 AS (SELECT *, sum(race_score_count) over (partition by racedesc)::int as race_count,
round(race_score_count / sum(race_score_count) over (partition by racedesc) * 100, 2) as per_cent_from_race
FROM data_1)
SELECT *, round(per_cent_from_race / sum(per_cent_from_race) over (partition by perf) * 100, 2) as weighted_per_cent_from_perf
FROM data_2
ORDER BY perf_scoreid
''', connect)
df_perf_race
| racedesc | perf | perf_scoreid | race_score_count | race_count | per_cent_from_race | weighted_per_cent_from_perf | |
|---|---|---|---|---|---|---|---|
| 0 | White | 90-day meets | 0 | 23 | 193 | 11.92 | 34.55 |
| 1 | Asian | 90-day meets | 0 | 4 | 34 | 11.76 | 34.09 |
| 2 | Two or more races | 90-day meets | 0 | 1 | 18 | 5.56 | 16.12 |
| 3 | Black or African American | 90-day meets | 0 | 3 | 57 | 5.26 | 15.25 |
| 4 | White | PIP | 1 | 6 | 193 | 3.11 | 23.28 |
| 5 | Two or more races | PIP | 1 | 1 | 18 | 5.56 | 41.62 |
| 6 | Black or African American | PIP | 1 | 1 | 57 | 1.75 | 13.10 |
| 7 | Asian | PIP | 1 | 1 | 34 | 2.94 | 22.01 |
| 8 | Hispanic | Needs Improvement | 2 | 1 | 4 | 25.00 | 51.68 |
| 9 | White | Needs Improvement | 2 | 5 | 193 | 2.59 | 5.35 |
| 10 | Two or more races | Needs Improvement | 2 | 1 | 18 | 5.56 | 11.49 |
| 11 | Black or African American | Needs Improvement | 2 | 7 | 57 | 12.28 | 25.39 |
| 12 | Asian | Needs Improvement | 2 | 1 | 34 | 2.94 | 6.08 |
| 13 | Two or more races | Fully Meets | 3 | 10 | 18 | 55.56 | 16.52 |
| 14 | Asian | Fully Meets | 3 | 20 | 34 | 58.82 | 17.49 |
| 15 | Black or African American | Fully Meets | 3 | 37 | 57 | 64.91 | 19.30 |
| 16 | American Indian or Alaska Native | Fully Meets | 3 | 2 | 4 | 50.00 | 14.87 |
| 17 | Hispanic | Fully Meets | 3 | 2 | 4 | 50.00 | 14.87 |
| 18 | White | Fully Meets | 3 | 110 | 193 | 56.99 | 16.95 |
| 19 | American Indian or Alaska Native | Exceeds | 4 | 2 | 4 | 50.00 | 56.61 |
| 20 | White | Exceeds | 4 | 18 | 193 | 9.33 | 10.56 |
| 21 | Two or more races | Exceeds | 4 | 3 | 18 | 16.67 | 18.87 |
| 22 | Asian | Exceeds | 4 | 3 | 34 | 8.82 | 9.99 |
| 23 | Black or African American | Exceeds | 4 | 2 | 57 | 3.51 | 3.97 |
| 24 | Asian | Exceptional | 5 | 1 | 34 | 2.94 | 27.25 |
| 25 | White | Exceptional | 5 | 5 | 193 | 2.59 | 24.00 |
| 26 | Black or African American | Exceptional | 5 | 3 | 57 | 5.26 | 48.75 |
| 27 | Two or more races | N/A- too early to review | 9 | 2 | 18 | 11.11 | 16.25 |
| 28 | Asian | N/A- too early to review | 9 | 4 | 34 | 11.76 | 17.20 |
| 29 | Black or African American | N/A- too early to review | 9 | 4 | 57 | 7.02 | 10.27 |
| 30 | White | N/A- too early to review | 9 | 26 | 193 | 13.47 | 19.70 |
| 31 | Hispanic | N/A- too early to review | 9 | 1 | 4 | 25.00 | 36.57 |
# на двух графиках изобразим оценку перформанса по расовым группам
fig, ax = plt.subplots()
sns.barplot(df_perf_race, y='per_cent_from_race', hue='perf', x='racedesc', palette='magma', ax=ax)
sns.move_legend(ax, bbox_to_anchor=(1, 1), loc='upper left', title='Оценка')
for container in ax.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Зависимость оценки перформанса от расы сотрудника')
fig.text(y=0.92, x=0.125, s='(% людей с определенной оценкой перформанса от расовой группы)', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('Процент расовой группы, имеющих такую оценку')
upper_line(ax)
fig2, ax2 = plt.subplots()
sns.barplot(df_perf_race, y='weighted_per_cent_from_perf', x='perf', hue='racedesc', palette='magma', ax=ax2)
sns.move_legend(ax2, bbox_to_anchor=(1, 1), loc='upper left', title='Раса')
for container in ax2.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax2.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax2.set_title('Зависимость оценки перформанса от расы сотрудника', y=1.04)
fig2.text(y=0.92, x=0.125, s='(% людей определенной расы от групп одинаковой оценки перформанса, \nвзвешенный по числу людей в расовой группе)', fontsize=12)
ax2.set_xlabel('')
ax2.set_ylabel('% Процент расовой группы, имеющих такую оценку')
ax2.plot([.03, 0.9], [0.96, 0.96], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
ax2.plot([.03, 0.9], [0.85, 0.85], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
plt.show()
В каждой расовой группе превалирующая оценка опять же Fully Meets. Между расами white, black и asian нет значитмых различий, однако заметно наличие большего процента людей расы black как для оценки 'Needs Improvement', так и для оценки 'Exceptional'.
# рассчитаем относительные показатели по каждой оценке перформанса и каждой группе семейного положения
df_perf_marital = pd.read_sql(''' with data_1 AS (SELECT maritaldesc, "Performance Score" as perf, perf_scoreid, count(*) as marital_score_count
FROM hr_dataset
GROUP BY maritaldesc, "Performance Score", perf_scoreid),
data_2 AS (SELECT *, sum(marital_score_count) over (partition by maritaldesc)::int as marital_count,
round(marital_score_count / sum(marital_score_count) over (partition by maritaldesc) * 100, 2) as per_cent_from_marital
FROM data_1)
SELECT *, round(per_cent_from_marital / sum(per_cent_from_marital) over (partition by perf) * 100, 2) as weighted_per_cent_from_perf
FROM data_2
ORDER BY perf_scoreid
''', connect)
df_perf_marital
| maritaldesc | perf | perf_scoreid | marital_score_count | marital_count | per_cent_from_marital | weighted_per_cent_from_perf | |
|---|---|---|---|---|---|---|---|
| 0 | Divorced | 90-day meets | 0 | 3 | 30 | 10.00 | 31.56 |
| 1 | Single | 90-day meets | 0 | 13 | 137 | 9.49 | 29.95 |
| 2 | Married | 90-day meets | 0 | 15 | 123 | 12.20 | 38.50 |
| 3 | Single | PIP | 1 | 4 | 137 | 2.92 | 20.14 |
| 4 | Separated | PIP | 1 | 1 | 12 | 8.33 | 57.45 |
| 5 | Married | PIP | 1 | 4 | 123 | 3.25 | 22.41 |
| 6 | Married | Needs Improvement | 2 | 6 | 123 | 4.88 | 29.29 |
| 7 | Divorced | Needs Improvement | 2 | 2 | 30 | 6.67 | 40.04 |
| 8 | Single | Needs Improvement | 2 | 7 | 137 | 5.11 | 30.67 |
| 9 | Single | Fully Meets | 3 | 81 | 137 | 59.12 | 21.42 |
| 10 | Separated | Fully Meets | 3 | 8 | 12 | 66.67 | 24.16 |
| 11 | Widowed | Fully Meets | 3 | 3 | 8 | 37.50 | 13.59 |
| 12 | Divorced | Fully Meets | 3 | 16 | 30 | 53.33 | 19.32 |
| 13 | Married | Fully Meets | 3 | 73 | 123 | 59.35 | 21.51 |
| 14 | Single | Exceeds | 4 | 12 | 137 | 8.76 | 18.68 |
| 15 | Divorced | Exceeds | 4 | 4 | 30 | 13.33 | 28.43 |
| 16 | Separated | Exceeds | 4 | 2 | 12 | 16.67 | 35.55 |
| 17 | Married | Exceeds | 4 | 10 | 123 | 8.13 | 17.34 |
| 18 | Divorced | Exceptional | 5 | 1 | 30 | 3.33 | 15.84 |
| 19 | Widowed | Exceptional | 5 | 1 | 8 | 12.50 | 59.47 |
| 20 | Single | Exceptional | 5 | 6 | 137 | 4.38 | 20.84 |
| 21 | Married | Exceptional | 5 | 1 | 123 | 0.81 | 3.85 |
| 22 | Single | N/A- too early to review | 9 | 14 | 137 | 10.22 | 10.96 |
| 23 | Separated | N/A- too early to review | 9 | 1 | 12 | 8.33 | 8.93 |
| 24 | Divorced | N/A- too early to review | 9 | 4 | 30 | 13.33 | 14.29 |
| 25 | Widowed | N/A- too early to review | 9 | 4 | 8 | 50.00 | 53.61 |
| 26 | Married | N/A- too early to review | 9 | 14 | 123 | 11.38 | 12.20 |
# на двух графиках изобразим оценку перформанса по группам семейного положения
fig, ax = plt.subplots()
sns.barplot(df_perf_marital, y='per_cent_from_marital', hue='perf', x='maritaldesc', palette='magma', ax=ax)
sns.move_legend(ax, bbox_to_anchor=(1, 1), loc='upper left', title='Оценка')
for container in ax.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax.set_title('Зависимость оценки перформанса от семейного положения сотрудника')
ax.text(y=1.05, x=0, transform=ax.transAxes, s='(% людей с определенной оценкой перформанса от группы по семейному положению)', fontsize=12)
ax.set_xlabel('')
ax.set_ylabel('% Процент людей группы сем. полож-я, имеющих такую оценку')
upper_line(ax)
fig2, ax2 = plt.subplots()
sns.barplot(df_perf_marital, y='weighted_per_cent_from_perf', x='perf', hue='maritaldesc', palette='magma', ax=ax2)
sns.move_legend(ax2, bbox_to_anchor=(1, 1), loc='upper left', title='Семейное\nположение')
for container in ax2.containers:
for rect in container.patches:
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
if height == height:
ax2.text(x, y, f'{height/100: .1%}', ha='center', va='bottom', fontsize=6)
ax2.set_title('Зависимость оценки перформанса от семейного положения сотрудника', y=1.04)
ax2.text(y=0.975, x=-0.03, transform=ax.transAxes, s='(% людей определенного сем. положения от групп одинаковой оценки перформанса, \nвзвешенный по числу людей в группе данного семейного положения)', fontsize=12)
ax2.set_xlabel('')
ax2.set_ylabel('% Процент людей группы сем. полож-я, имеющих такую оценку')
ax2.plot([.03, 0.9], [0.96, 0.96], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
ax2.plot([.03, 0.9], [0.85, 0.85], transform=fig.transFigure, clip_on=False, color='black', linewidth=1)
plt.show()
В каждой группе по семейному положению превалирующая оценка опять же Fully Meets, за исключением группы widowed - в ней чаще всего получают оценку "NA - too early to review". Между группами single и married нет значитмых различий внутри каждой оценки, кроме Exceptional - ее намного чаще получают неженатые/незамужние люди.
# визуализируем зависимость между оценкой перформанса и стажем сотрудника
fig, ax = plt.subplots()
sns.boxplot(df_experience, x='Days Employed', y='Performance Score', palette=sns.color_palette('magma', n_colors=8)[1:], ax=ax)
plt.title('Зависимость оценки перформанса')
fig.text(y=0.92, x=0.125, s='от продолжительности работы в компании', fontsize=12)
plt.xlabel('Продолжительность работы в компании, дней', loc='left')
plt.ylabel('')
upper_line(ax)
Ожидаемо, наибольшая медиана стажа в компании у тех, кто получает оценку Exceed, при этом довольно высокая медиана и для тех, у кого PIP - необходимо оратить внимание на политику компании в этой сфере и решить, допустимо ли оставлять на долгий срок сотрудников с низким перформансом.
# выделим необходимые данные в отдельный датафрейм
df_experience_dep = pd.read_sql(''' SELECT id, "Days Employed", sex, department, "Manager Name" as manager, "Pay Rate" as pay
FROM hr_dataset
WHERE "Date of Termination" is not NULL
''', connect)
df_experience_dep
| id | Days Employed | sex | department | manager | pay | |
|---|---|---|---|---|---|---|
| 0 | 4 | 58 | Female | Admin Offices | Brandon R. LeBlanc | 21.50 |
| 1 | 6 | 730 | Female | Admin Offices | Brandon R. LeBlanc | 20.50 |
| 2 | 8 | 1636 | Male | Admin Offices | Janet King | 55.00 |
| 3 | 13 | 432 | Female | IT/IS | Simon Roup | 43.00 |
| 4 | 14 | 27 | Female | IT/IS | Simon Roup | 48.50 |
| ... | ... | ... | ... | ... | ... | ... |
| 98 | 272 | 1334 | Male | Sales | John Smith | 55.00 |
| 99 | 290 | 899 | Female | Sales | Debra Houlihan | 60.25 |
| 100 | 295 | 765 | Female | Software Engineering | Alex Sweetwater | 48.50 |
| 101 | 297 | 1400 | Male | Software Engineering | Alex Sweetwater | 52.25 |
| 102 | 300 | 421 | Male | Software Engineering | Alex Sweetwater | 45.42 |
103 rows × 6 columns
# для ускорения построения графиков создадим новую функцию
def plotter(pltype, data, x, ax=None, y=None,
palette=None, with_darkest=True, hue=None,
style=None, title=None, subtitle=None, xlabel=None,
ylabel=None, no_legend=False, legend_out=True, legend_title=None, hue_order=None,
upper_lines=True, grid=True, color=None, figsize=(12, 6), **kwargs):
if ax is None:
fig, ax = plt.subplots(figsize=figsize)
if (color is None) and (palette is None):
palette = 'magma'
if (hue is not None) and (palette is None) and not with_darkest:
palette = sns.color_palette(palette, n_colors=data[hue].unique().size + 1)[1:]
elif not with_darkest and x is not None and y is not None:
palette = sns.color_palette(palette, n_colors=min(data[x].unique().size + 1, data[y].unique().size + 1))[1:]
plot_functions = {
'scatter': sns.scatterplot,
'box': sns.boxplot,
'bar': sns.barplot,
'hist': sns.histplot,
'line': sns.lineplot
}
plot_functions[pltype](data, ax=ax, x=x, y=y, palette=palette, hue=hue, hue_order=hue_order, color=color, **kwargs)
if pltype not in plot_functions:
raise ValueError('Неизвестный тип графика')
if hue is not None and legend_out:
sns.move_legend(ax, bbox_to_anchor=(1, 1), loc='upper left', title=legend_title)
ax.set_title(title)
if subtitle is not None:
ax.text(y=1.05, x=0, transform=ax.transAxes, s=subtitle, fontsize=12)
if xlabel is not None:
ax.set_xlabel(xlabel, loc='left')
if ylabel is not None:
ax.set_ylabel(ylabel)
if upper_lines:
ax.plot([0.05, 0.9], [0.99, 0.99], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
ax.plot([0.05, 0.9], [0.90, 0.90], transform=fig.transFigure, clip_on=False, color='black', linewidth=0.7)
if no_legend:
ax.get_legend().remove()
if grid:
ax.grid(alpha=0.3, zorder=1)
return ax
ax = plotter('box', data=df_experience_dep, x='Days Employed', y='department',
title='Продолжительность работы в компании', subtitle='сотрудников по департаментам (рассматриваем только уволившихся)', xlabel='Количество отработанных дней', ylabel='',
with_darkest=False)
Заметно довольно низкое по сравнению с остальными отделами значение медианного стажа в компании для департамента IT/IS - причем данный департамент имеет не самый короткий срок существования. Скорее всего, следует обратить внимание на удержание сотрудников данное департамента.
# выделим необходимые данные в отдельный датафрейм
df_experience_dep_all = pd.read_sql(''' SELECT id, "Days Employed", sex, department, "Manager Name" as manager, "Pay Rate" as pay
FROM hr_dataset
''', connect)
df_experience_dep_all
| id | Days Employed | sex | department | manager | pay | |
|---|---|---|---|---|---|---|
| 0 | 1 | 3317 | Female | Admin Offices | Brandon R. LeBlanc | 28.50 |
| 1 | 2 | 1420 | Male | Admin Offices | Brandon R. LeBlanc | 23.00 |
| 2 | 3 | 1154 | Male | Admin Offices | Brandon R. LeBlanc | 29.00 |
| 3 | 4 | 58 | Female | Admin Offices | Brandon R. LeBlanc | 21.50 |
| 4 | 5 | 940 | Female | Admin Offices | Brandon R. LeBlanc | 16.56 |
| ... | ... | ... | ... | ... | ... | ... |
| 305 | 306 | 289 | Male | IT/IS | Brian Champaigne | 52.25 |
| 306 | 307 | 284 | Male | IT/IS | Brian Champaigne | 51.00 |
| 307 | 308 | 284 | Male | IT/IS | Brian Champaigne | 46.00 |
| 308 | 309 | 220 | Male | IT/IS | Brian Champaigne | 45.00 |
| 309 | 310 | 220 | Male | IT/IS | Brian Champaigne | 45.00 |
310 rows × 6 columns
ax = plotter('scatter', data=df_experience_dep, x='Days Employed', y='manager', hue='department',
subtitle='под руководством каждого менеджера (рассмотрим только уволенных)',
ylabel='', figsize=(12, 12), s=80, zorder=6)
plotter('box', data=df_experience_dep, x='Days Employed', y='manager', xlabel='Количество отработанных дней', color=sns.color_palette('magma_r')[0], upper_lines=False, ylabel='', ax=ax, zorder=5)
ax.set_xticks(list(range(0, 5000, 500)))
ax.set_title('Продолжительность работы сотрудников в компании', y=1.05)
plt.show()
Четкие зависимости между стажем и менеджером вывести довольно сложно, так как количество уволившихся подчиненных различное у каждого менеджера, однако довольно незначительное, чтобы были видны явные тенденции. Возможно, стоит обратить внимание на довольно низкую медиану и узкое распределение стажа в компании сотрудников под руководством Kissy Sullivan.
ax = plotter('scatter', data=df_experience_dep, x='pay', y='Days Employed', hue='department',
title='Зависимость срока работы в компании от оплаты',
subtitle='в разбивке по департаментам (рассмотрим только уволенных)',
ylabel='Количество отработанных дней', xlabel='Заработная плата, $/час', s=80)
ax.set_xticks(list(range(10, 70, 5)))
plt.show()
Наблюдается положительная зависимость между зарплатой и стажем в компании для департамента Software Engineering - однако скорее стаж влияет на зп, а не наоборот. Интересно, что для всех остальных департаментов зависимости не наблюдается вообще.
# выделим необходимые данные в отдельный датафрейм
df_perf_dep = pd.read_sql(''' SELECT id, "Days Employed", perf_scoreid, "Performance Score" as performance, sex, department, "Manager Name" as manager, "Pay Rate" as pay
FROM hr_dataset
order by perf_scoreid
''', connect)
df_perf_dep
| id | Days Employed | perf_scoreid | performance | sex | department | manager | pay | |
|---|---|---|---|---|---|---|---|---|
| 0 | 281 | 1056 | 0 | 90-day meets | Male | Sales | John Smith | 55.00 |
| 1 | 293 | 1476 | 0 | 90-day meets | Female | Software Engineering | Alex Sweetwater | 56.00 |
| 2 | 213 | 2211 | 0 | 90-day meets | Male | Production | Kissy Sullivan | 25.00 |
| 3 | 270 | 1014 | 0 | 90-day meets | Female | Sales | Lynn Daneault | 55.00 |
| 4 | 211 | 602 | 0 | 90-day meets | Female | Production | Brannon Miller | 25.00 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 305 | 151 | 972 | 9 | N/A- too early to review | Female | Production | Michael Albert | 20.00 |
| 306 | 43 | 972 | 9 | N/A- too early to review | Male | IT/IS | Peter Monroe | 49.10 |
| 307 | 39 | 972 | 9 | N/A- too early to review | Male | IT/IS | Peter Monroe | 43.00 |
| 308 | 249 | 26 | 9 | N/A- too early to review | Female | Production | David Stanley | 26.00 |
| 309 | 95 | 508 | 9 | N/A- too early to review | Female | Production | Michael Albert | 19.75 |
310 rows × 8 columns
g = sns.FacetGrid(df_perf_dep, col='department', col_wrap=3, palette='magma', hue='department', sharex=False)
g.map_dataframe(sns.histplot, y='performance')
g.add_legend()
g.set_titles(col_template="{col_name}")
plt.show()
Наиболее стабильный перформанс показывают сотрудники депаратмента Admin Offices. Заметно наличие большого количества высоких оценок Exceeds и Exceptional в департаменте IT/IS.
g = sns.FacetGrid(df_perf_dep, col='manager', col_wrap=3, palette='magma', hue='department', sharex=False)
g.map_dataframe(sns.histplot, y='performance')
g.add_legend()
g.set_titles(col_template="{col_name}")
g.set(xticks=list(range(0, 15, 2)))
plt.show()
В данном разделе стоит обратить внимание на случаи, когда количество низких оценок перформанса относительно высокое. Относительно высокое количество оценок PIP и Needs Improvement имеют подчиненные менеджеров Brannon Miller и Michael Albert.
Такие случаи могут иметь несколько объяснений:
ax = plotter('scatter', data=df_perf_dep, y='pay', x='performance', hue='department',
title='Зависимость перформанса и оплаты',
subtitle='в разбивке по департаментам',
xlabel='', ylabel='Заработная плата, $/час', s=80)
plt.show()
Зависимости между оценкой перформанса и заработной платой не наблюдается ни в одном из департаментов.
# выделим в отдельный датафрейм данные по ушедшим сотрудникам
df_left = pd.read_sql('''SELECT id, "Reason For Term", sex, department, "Manager Name", "Days Employed", "Date of Termination"
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
''', connect)
df_left
| id | Reason For Term | sex | department | Manager Name | Days Employed | Date of Termination | |
|---|---|---|---|---|---|---|---|
| 0 | 6 | career change | Female | Admin Offices | Brandon R. LeBlanc | 730 | 2013-09-25 |
| 1 | 8 | career change | Male | Admin Offices | Janet King | 1636 | 2015-08-15 |
| 2 | 13 | performance | Female | IT/IS | Simon Roup | 432 | 2015-09-12 |
| 3 | 14 | no-call, no-show | Female | IT/IS | Simon Roup | 27 | 2015-03-15 |
| 4 | 15 | no-call, no-show | Male | IT/IS | Simon Roup | 6 | 2015-02-22 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 97 | 272 | relocation out of area | Male | Sales | John Smith | 1334 | 2014-10-31 |
| 98 | 290 | maternity leave - did not return | Female | Sales | Debra Houlihan | 899 | 2014-04-24 |
| 99 | 295 | attendance | Female | Software Engineering | Alex Sweetwater | 765 | 2013-06-05 |
| 100 | 297 | Another position | Male | Software Engineering | Alex Sweetwater | 1400 | 2015-09-07 |
| 101 | 300 | medical issues | Male | Software Engineering | Alex Sweetwater | 421 | 2014-04-15 |
102 rows × 7 columns
# рассчитаем общее количество ушедших, а также количество ушедших женщин и мужчин
df_reasons_sex_count = pd.read_sql('''SELECT 'Total' AS reasons, count(CASE WHEN sex = 'Female' THEN 1 END) as women,
count(CASE WHEN sex = 'Male' THEN 1 END) as men, count(*) AS all
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
''', connect)
df_reasons_sex_count
| reasons | women | men | all | |
|---|---|---|---|---|
| 0 | Total | 59 | 43 | 102 |
Для дальнейшего анализа предлагается распределить причины ухода на 3 группы:
# распределеим ушедших на группы
df_reasons_sex = pd.read_sql('''SELECT
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
"Reason For Term" AS reasons,
count(CASE WHEN sex = 'Female' THEN 1 END) as women,
count(CASE WHEN sex = 'Male' THEN 1 END) as men, count(*) AS all
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
GROUP BY reason_group, "Reason For Term"
ORDER BY reason_group, "all" DESC''', connect)
df_reasons_sex
| reason_group | reasons | women | men | all | |
|---|---|---|---|---|---|
| 0 | 1. Cannot influence | Another position | 16 | 4 | 20 |
| 1 | 1. Cannot influence | career change | 4 | 5 | 9 |
| 2 | 1. Cannot influence | return to school | 4 | 1 | 5 |
| 3 | 1. Cannot influence | relocation out of area | 3 | 2 | 5 |
| 4 | 1. Cannot influence | retiring | 1 | 3 | 4 |
| 5 | 1. Cannot influence | military | 2 | 2 | 4 |
| 6 | 1. Cannot influence | medical issues | 1 | 2 | 3 |
| 7 | 1. Cannot influence | maternity leave - did not return | 2 | 1 | 3 |
| 8 | 2. Can influence | unhappy | 9 | 5 | 14 |
| 9 | 2. Can influence | more money | 6 | 5 | 11 |
| 10 | 2. Can influence | hours | 3 | 6 | 9 |
| 11 | 3. Try not hire | attendance | 3 | 4 | 7 |
| 12 | 3. Try not hire | performance | 2 | 2 | 4 |
| 13 | 3. Try not hire | no-call, no-show | 2 | 1 | 3 |
| 14 | 3. Try not hire | gross misconduct | 1 | 0 | 1 |
# посмотрим на причины ухода в зависимости от пола
df_reasons_sex_plot = pd.read_sql('''SELECT
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
"Reason For Term" AS reasons,
'Female' as sex,
count(CASE WHEN sex = 'Female' THEN 1 END) as count_sex, count(*) AS all
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
GROUP BY reason_group, "Reason For Term"
UNION ALL
SELECT
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
"Reason For Term" AS reasons,
'Male' as sex,
count(CASE WHEN sex = 'Male' THEN 1 END) as count_sex, count(*) AS all
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
GROUP BY reason_group, "Reason For Term"
ORDER BY reason_group, reasons''', connect)
plotter(data=df_reasons_sex_plot, pltype='bar', x='reason_group', y='count_sex',
hue='sex', estimator='sum', palette='magma', errorbar=None,
title='Количество прекративших работу', subtitle='по тем или иным причинам', xlabel='Группа причин ухода', ylabel='Количество людей, прекративших работу в должности')
plt.show()
Заметно больше сотрудников женского пола уходит из компании по причинам первой группы - тем, на которые компания не в силах влиять. Остальные группы примерно одинаковы между гендерными группами.
# выдедим в отдельный датафрейм необходимые данные и рассчитаем процент ушедших по той или иной причине
df_department_reason = pd.read_sql('''with data_1 AS (SELECT department,
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group, "Reason For Term" AS reasons,
count(*) AS reason_count, sum(count(*)) over (partition by department) ::int as total_left_per_department
FROM hr_dataset
WHERE ("Date of Termination" is not NULL) AND ("Reason For Term" != 'N/A - still employed')
GROUP BY department, reason_group, reasons
),
data_2 AS (SELECT department, count(*) as total_in_department
FROM hr_dataset
GROUP BY department)
SELECT *, round(reason_count/total_in_department::numeric * 100, 2) AS reason_to_department FROM data_1 LEFT JOIN data_2 USING (department)
ORDER BY reason_group, reasons
''', connect)
df_department_reason
| department | reason_group | reasons | reason_count | total_left_per_department | total_in_department | reason_to_department | |
|---|---|---|---|---|---|---|---|
| 0 | Production | 1. Cannot influence | Another position | 17 | 83 | 208 | 8.17 |
| 1 | IT/IS | 1. Cannot influence | Another position | 1 | 10 | 50 | 2.00 |
| 2 | Software Engineering | 1. Cannot influence | Another position | 1 | 3 | 10 | 10.00 |
| 3 | Sales | 1. Cannot influence | Another position | 1 | 4 | 31 | 3.23 |
| 4 | Admin Offices | 1. Cannot influence | career change | 2 | 2 | 10 | 20.00 |
| 5 | IT/IS | 1. Cannot influence | career change | 1 | 10 | 50 | 2.00 |
| 6 | Production | 1. Cannot influence | career change | 6 | 83 | 208 | 2.88 |
| 7 | Sales | 1. Cannot influence | maternity leave - did not return | 1 | 4 | 31 | 3.23 |
| 8 | Production | 1. Cannot influence | maternity leave - did not return | 2 | 83 | 208 | 0.96 |
| 9 | Production | 1. Cannot influence | medical issues | 1 | 83 | 208 | 0.48 |
| 10 | IT/IS | 1. Cannot influence | medical issues | 1 | 10 | 50 | 2.00 |
| 11 | Software Engineering | 1. Cannot influence | medical issues | 1 | 3 | 10 | 10.00 |
| 12 | Production | 1. Cannot influence | military | 4 | 83 | 208 | 1.92 |
| 13 | Sales | 1. Cannot influence | relocation out of area | 1 | 4 | 31 | 3.23 |
| 14 | Production | 1. Cannot influence | relocation out of area | 4 | 83 | 208 | 1.92 |
| 15 | Production | 1. Cannot influence | retiring | 4 | 83 | 208 | 1.92 |
| 16 | Production | 1. Cannot influence | return to school | 5 | 83 | 208 | 2.40 |
| 17 | IT/IS | 2. Can influence | hours | 3 | 10 | 50 | 6.00 |
| 18 | Production | 2. Can influence | hours | 6 | 83 | 208 | 2.88 |
| 19 | Production | 2. Can influence | more money | 11 | 83 | 208 | 5.29 |
| 20 | Production | 2. Can influence | unhappy | 14 | 83 | 208 | 6.73 |
| 21 | Production | 3. Try not hire | attendance | 5 | 83 | 208 | 2.40 |
| 22 | Sales | 3. Try not hire | attendance | 1 | 4 | 31 | 3.23 |
| 23 | Software Engineering | 3. Try not hire | attendance | 1 | 3 | 10 | 10.00 |
| 24 | Production | 3. Try not hire | gross misconduct | 1 | 83 | 208 | 0.48 |
| 25 | Production | 3. Try not hire | no-call, no-show | 1 | 83 | 208 | 0.48 |
| 26 | IT/IS | 3. Try not hire | no-call, no-show | 2 | 10 | 50 | 4.00 |
| 27 | IT/IS | 3. Try not hire | performance | 2 | 10 | 50 | 4.00 |
| 28 | Production | 3. Try not hire | performance | 2 | 83 | 208 | 0.96 |
# визуализируем причины ухода по департаментам
fig, axes = plt.subplots(nrows=5, figsize=(10, 17), gridspec_kw={'hspace': 0.6})
departments = df_department_reason.department.unique().tolist()
magma_palette = sns.color_palette('magma', n_colors=15)
order = sorted(df_department_reason.reasons.unique())
m_palette={i: j for i, j in zip(order, magma_palette)}
for row in range(5):
department = departments.pop()
ax = axes[row]
plotter('bar', data=df_department_reason[df_department_reason.department == department],
x='reason_group', y='reason_to_department', hue='reasons',
ax=ax, upper_lines=False,
title=department)
plt.suptitle('Процент ушедших по тем или иным причинам', y=0.99, x=0.384)
fig.text(s='от всех сотрудников департамента', x=0.05, y=0.96, fontsize=16)
upper_line(ax, is_suptitle=True)
Люди, ушедшие по причинам, на которые компания может повлиять (группа причин 2), были только в департаментах IT/IS и Production. Из Admin Offices люди уходили только по причине career change. Отдел Production покинули люди по всeм возможным причинам, однако стоит выделить наиболее частые: another position (cannot influence) и unhappy (can influence).
# выделим ушедших по причинам второй группы (сожем повлиять) в отдельный датафрейм
df_left_second_group = pd.read_sql(''' with ages as (SELECT DISTINCT
age, CASE WHEN age <= 17 THEN '17-'
WHEN age BETWEEN 18 AND 24 THEN '18-24'
WHEN age BETWEEN 25 AND 29 THEN '25-29'
WHEN age BETWEEN 30 AND 34 THEN '30-34'
WHEN age BETWEEN 35 AND 39 THEN '35-39'
WHEN age BETWEEN 40 AND 44 THEN '40-44'
WHEN age BETWEEN 45 AND 49 THEN '45-49'
WHEN age BETWEEN 50 AND 54 THEN '50-54'
WHEN age BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset),
pays as (SELECT position, percentile_cont(0.5) within group (order by "Pay Rate") as median_pay
FROM hr_dataset
GROUP BY position),
data as (SELECT "Date of Hire", "Date of Termination", position,
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
"Reason For Term" as reason,
department, count(*) over (partition by department) as total_in_dep,
racedesc, count(*) over (partition by racedesc) as total_per_race,
"Manager Name" as manager, count(*) over (partition by "Manager Name") as total_per_manager,
"Pay Rate" as pay,
age_group, count(*) over (partition by age_group) AS total_in_age_group
FROM hr_dataset LEFT JOIN ages ON hr_dataset.age = ages.age
)
SELECT reason_group, reason, department, data.position, count(*) over (partition by department) AS department_valcount, total_in_dep, round(count(*) over (partition by department) / total_in_dep::numeric * 100, 2) AS per_cent_dep,
racedesc, count(*) over (partition by racedesc) AS racedesc_valcount, total_per_race, round(count(*) over (partition by racedesc) / total_per_race::numeric * 100, 2) AS per_cent_race,
age_group, count(*) over (partition by age_group) AS age_valcount, total_in_age_group, round(count(*) over (partition by age_group) / total_in_age_group::numeric * 100, 2) AS per_cent_age,
manager, count(*) over (partition by manager) AS manager_valcount, total_per_manager, round(count(*) over (partition by manager) / total_per_manager::numeric * 100, 2) AS per_cent_manager,
pay, "Hourly Mid" as mid_pay, median_pay,
CASE WHEN pay >= median_pay THEN 'no' ELSE 'yes' END AS pay_less_than_median
FROM data LEFT JOIN salary_grid ON data.position = salary_grid."Position" LEFT JOIN pays ON data.position = pays.position
WHERE reason_group = '2. Can influence'
order by pay_less_than_median
''', connect)
df_left_second_group.head()
| reason_group | reason | department | position | department_valcount | total_in_dep | per_cent_dep | racedesc | racedesc_valcount | total_per_race | ... | total_in_age_group | per_cent_age | manager | manager_valcount | total_per_manager | per_cent_manager | pay | mid_pay | median_pay | pay_less_than_median | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2. Can influence | unhappy | Production | Production Technician I | 31 | 208 | 14.9 | White | 26 | 193 | ... | 46 | 13.04 | Webster Butler | 4 | 21 | 19.05 | 23.5 | 19.23 | 19.25 | no |
| 1 | 2. Can influence | unhappy | Production | Production Technician II | 31 | 208 | 14.9 | Asian | 4 | 34 | ... | 36 | 5.56 | Amy Dunn | 8 | 21 | 38.10 | 28.0 | 23.08 | 25.00 | no |
| 2 | 2. Can influence | hours | Production | Production Technician II | 31 | 208 | 14.9 | White | 26 | 193 | ... | 64 | 7.81 | Amy Dunn | 8 | 21 | 38.10 | 29.0 | 23.08 | 25.00 | no |
| 3 | 2. Can influence | more money | Production | Production Technician I | 31 | 208 | 14.9 | White | 26 | 193 | ... | 38 | 15.79 | Amy Dunn | 8 | 21 | 38.10 | 24.0 | 19.23 | 19.25 | no |
| 4 | 2. Can influence | unhappy | Production | Production Technician I | 31 | 208 | 14.9 | White | 26 | 193 | ... | 38 | 15.79 | David Stanley | 1 | 21 | 4.76 | 21.0 | 19.23 | 19.25 | no |
5 rows × 23 columns
plotter(pltype='hist', data=df_left_second_group.sort_values(by='department'), x='position', hue='department', multiple='stack', xlabel='',
title='Должности уволенных людей', subtitle='Покинувшие компанию по причинам группы 2 (Can influence)',
edgecolor=None, shrink=0.6)
plt.xticks(rotation=20)
plt.show()
Подавляющее количество сотрудников, ушедших по причинам, на которые компания может повлиять, были на должностях Production Technician I и II, департамента Production.
ax = plotter(pltype='hist', data=df_left_second_group[df_left_second_group['reason'] == 'more money'], x='pay_less_than_median', hue='position', multiple='dodge', xlabel='',
title='Платили ли в компании ушедшим сотрудникам меньше медианы должности?', subtitle='Группа уволившихся по причине "more money"',
edgecolor=None, shrink=0.6)
ax.set_yticks(list(range(0, 5)), labels=list(range(0, 5)))
plt.show()
У 4 из 11 людей, которые покинули компанию по причине more money, заработная плата действительно была ниже медианы по должности.
ax = plotter(pltype='hist', data=df_left_second_group[df_left_second_group['reason'] == 'hours'], x='pay_less_than_median', hue='position', multiple='dodge', xlabel='',
title='Платили ли в компании ушедшим сотрудникам меньше медианы должности?', subtitle='Группа уволившихся по причине "hours"',
edgecolor=None, shrink=0.6)
ax.set_yticks(list(range(0, 3)), labels=list(range(0, 3)))
plt.show()
У 4 из 9 людей, которые покинули компанию по причине hours, заработная плата была ниже медианы по должности.
plotter(pltype='hist', data=df_left_second_group[df_left_second_group['reason'] == 'unhappy'], x='pay_less_than_median', hue='position', multiple='dodge', xlabel='',
title='Платили ли в компании ушедшим сотрудникам меньше медианы должности?', subtitle='Группа уволившихся по причине "unhappy"',
edgecolor=None, shrink=0.6)
plt.show()
У 5 из 14 людей, которые покинули компанию по причине unhappy, заработная плата была ниже медианы по должности.
ax = plotter(pltype='bar', data=df_left_second_group.sort_values(by='per_cent_manager'), x='manager', y='per_cent_manager',
ylabel='Процент уволившихся сотрудников от всех', xlabel='',
title='Процент уволившихся сотрудников', subtitle='от всех, работавших под руководством того или иного менеджера (по причинам группы 2 - Can influence)',
edgecolor=None)
manager_list = df_left_second_group.sort_values(by='per_cent_manager')['manager'].unique().tolist()
ax.set_xticklabels([i.replace(' ', '\n') for i in manager_list])
for container in ax.containers:
for rect, manager in zip(container, manager_list):
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height + 1
mark = df_left_second_group[df_left_second_group['manager'] == manager]['manager_valcount'].drop_duplicates().squeeze()
ax.text(x, y, mark, ha='center', va='bottom', fontsize=6)
plt.show()
Заметное количество сотрудников, уволившихся по причинам группы 2 (на которые компания может повлиять), работали под руководством менеджера Amy Dunn.
# выделим в отдельный датафрейм данные по 3 группе причин ухода (постараться не нанимать)
df_left_third_group = pd.read_sql(''' with ages as (SELECT DISTINCT
age, CASE WHEN age <= 17 THEN '17-'
WHEN age BETWEEN 18 AND 24 THEN '18-24'
WHEN age BETWEEN 25 AND 29 THEN '25-29'
WHEN age BETWEEN 30 AND 34 THEN '30-34'
WHEN age BETWEEN 35 AND 39 THEN '35-39'
WHEN age BETWEEN 40 AND 44 THEN '40-44'
WHEN age BETWEEN 45 AND 49 THEN '45-49'
WHEN age BETWEEN 50 AND 54 THEN '50-54'
WHEN age BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset),
pays as (SELECT position, percentile_cont(0.5) within group (order by "Pay Rate") as median_pay
FROM hr_dataset
GROUP BY position),
data as (SELECT "Date of Hire", "Date of Termination", position, "Performance Score" as performance,
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
"Reason For Term" as reason,
department, count(*) over (partition by department) as total_in_dep,
racedesc, count(*) over (partition by racedesc) as total_per_race,
"Manager Name" as manager, count(*) over (partition by "Manager Name") as total_per_manager,
"Pay Rate" as pay,
age_group, count(*) over (partition by age_group) AS total_in_age_group
FROM hr_dataset LEFT JOIN ages ON hr_dataset.age = ages.age
)
SELECT reason_group, reason, performance, department, data.position, count(*) over (partition by department) AS department_valcount, total_in_dep, round(count(*) over (partition by department) / total_in_dep::numeric * 100, 2) AS per_cent_dep,
racedesc, count(*) over (partition by racedesc) AS racedesc_valcount, total_per_race, round(count(*) over (partition by racedesc) / total_per_race::numeric * 100, 2) AS per_cent_race,
age_group, count(*) over (partition by age_group) AS age_valcount, total_in_age_group, round(count(*) over (partition by age_group) / total_in_age_group::numeric * 100, 2) AS per_cent_age,
manager, count(*) over (partition by manager) AS manager_valcount, total_per_manager, round(count(*) over (partition by manager) / total_per_manager::numeric * 100, 2) AS per_cent_manager,
pay, "Hourly Mid" as mid_pay, median_pay,
CASE WHEN pay >= median_pay THEN 'no' ELSE 'yes' END AS pay_less_than_median
FROM data LEFT JOIN salary_grid ON data.position = salary_grid."Position" LEFT JOIN pays ON data.position = pays.position
WHERE reason_group = '3. Try not hire'
order by pay_less_than_median
''', connect)
df_left_third_group
| reason_group | reason | performance | department | position | department_valcount | total_in_dep | per_cent_dep | racedesc | racedesc_valcount | ... | total_in_age_group | per_cent_age | manager | manager_valcount | total_per_manager | per_cent_manager | pay | mid_pay | median_pay | pay_less_than_median | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 3. Try not hire | gross misconduct | Exceeds | Production | Production Technician I | 9 | 208 | 4.33 | White | 9 | ... | 88 | 7.95 | Kelley Spirea | 2 | 22 | 9.09 | 21.0 | 19.23 | 19.250000 | no |
| 1 | 3. Try not hire | attendance | Fully Meets | Production | Production Technician I | 9 | 208 | 4.33 | Black or African American | 5 | ... | 46 | 2.17 | Amy Dunn | 1 | 21 | 4.76 | 24.0 | 19.23 | 19.250000 | no |
| 2 | 3. Try not hire | attendance | Fully Meets | Sales | Area Sales Manager | 1 | 31 | 3.23 | Black or African American | 5 | ... | 38 | 7.89 | John Smith | 1 | 14 | 7.14 | 55.0 | NaN | 55.000000 | no |
| 3 | 3. Try not hire | performance | Fully Meets | IT/IS | Database Administrator | 4 | 50 | 8.00 | White | 9 | ... | 88 | 7.95 | Simon Roup | 4 | 17 | 23.53 | 41.0 | 32.84 | 40.099998 | no |
| 4 | 3. Try not hire | performance | Fully Meets | IT/IS | Database Administrator | 4 | 50 | 8.00 | White | 9 | ... | 88 | 7.95 | Simon Roup | 4 | 17 | 23.53 | 43.0 | 32.84 | 40.099998 | no |
| 5 | 3. Try not hire | no-call, no-show | N/A- too early to review | IT/IS | Database Administrator | 4 | 50 | 8.00 | Black or African American | 5 | ... | 88 | 7.95 | Simon Roup | 4 | 17 | 23.53 | 40.1 | 32.84 | 40.099998 | no |
| 6 | 3. Try not hire | no-call, no-show | Fully Meets | IT/IS | Database Administrator | 4 | 50 | 8.00 | White | 9 | ... | 38 | 7.89 | Simon Roup | 4 | 17 | 23.53 | 48.5 | 32.84 | 40.099998 | no |
| 7 | 3. Try not hire | attendance | Needs Improvement | Production | Production Technician I | 9 | 208 | 4.33 | White | 9 | ... | 88 | 7.95 | Michael Albert | 1 | 22 | 4.55 | 17.0 | 19.23 | 19.250000 | yes |
| 8 | 3. Try not hire | attendance | Needs Improvement | Production | Production Technician I | 9 | 208 | 4.33 | White | 9 | ... | 88 | 7.95 | Ketsia Liebig | 1 | 21 | 4.76 | 16.0 | 19.23 | 19.250000 | yes |
| 9 | 3. Try not hire | no-call, no-show | N/A- too early to review | Production | Production Technician I | 9 | 208 | 4.33 | White | 9 | ... | 36 | 2.78 | Elijiah Gray | 1 | 22 | 4.55 | 18.0 | 19.23 | 19.250000 | yes |
| 10 | 3. Try not hire | attendance | Fully Meets | Production | Production Manager | 9 | 208 | 4.33 | White | 9 | ... | 38 | 7.89 | Janet King | 1 | 19 | 5.26 | 48.5 | NaN | 52.500000 | yes |
| 11 | 3. Try not hire | attendance | 90-day meets | Production | Production Technician I | 9 | 208 | 4.33 | White | 9 | ... | 64 | 4.69 | Kelley Spirea | 2 | 22 | 9.09 | 15.0 | 19.23 | 19.250000 | yes |
| 12 | 3. Try not hire | attendance | Needs Improvement | Software Engineering | Software Engineer | 1 | 10 | 10.00 | Black or African American | 5 | ... | 88 | 7.95 | Alex Sweetwater | 1 | 9 | 11.11 | 48.5 | NaN | 49.250000 | yes |
| 13 | 3. Try not hire | performance | Needs Improvement | Production | Production Technician I | 9 | 208 | 4.33 | Black or African American | 5 | ... | 64 | 4.69 | Kissy Sullivan | 2 | 22 | 9.09 | 15.0 | 19.23 | 19.250000 | yes |
| 14 | 3. Try not hire | performance | PIP | Production | Production Technician I | 9 | 208 | 4.33 | Two or more races | 1 | ... | 64 | 4.69 | Kissy Sullivan | 2 | 22 | 9.09 | 18.5 | 19.23 | 19.250000 | yes |
15 rows × 24 columns
plotter(pltype='hist', data=df_left_third_group.sort_values(by='department'), x='position', hue='department', multiple='stack', xlabel='',
title='Должности уволенных людей', subtitle='Покинувшие компанию по причинам группы 3 (Try not hire)',
edgecolor=None, shrink=0.6)
plt.show()
Значительное количество людей, которых не стоило нанимать, имели должности Database Administrator и Production Technician I - в дальнейшем отбору людей для этих позиций следует уделить больше внимания.
ax = plotter(pltype='hist', data=df_left_third_group[df_left_third_group['reason'] == 'performance'], x='performance', hue='performance', multiple='dodge', xlabel='',
title='Оценка перформанса у тех, кто был уволен', subtitle='Группа уволившихся по причине "performance"',
edgecolor=None, shrink=0.6)
ax.set_yticks(list(range(0, 3)), labels=list(range(0, 3)))
plt.show()
Неожиданно, но встречается 2 человека с оценкой перформанса Fully Meets среди тех, кто был уволен по причине 'performance'.
ax = plotter(pltype='hist', data=df_left_third_group[df_left_third_group['reason'] == 'attendance'], x='performance', hue='performance', multiple='dodge', xlabel='',
title='Оценка перформанса у тех, кто был уволен', subtitle='Группа уволившихся по причине "attendance"',
edgecolor=None, shrink=0.6)
ax.set_yticks(list(range(0, 4)), labels=list(range(0, 4)))
plt.show()
30% людей, уволенных по причине 'attendance', ранее получали плохую оценку перформанса.
ax = plotter(pltype='hist', data=df_left_third_group[df_left_third_group['reason'] == 'no-call, no-show'], x='performance', hue='performance', multiple='dodge', xlabel='',
title='Оценка перформанса у тех, кто был уволен', subtitle='Группа уволившихся по причине "no-call, no-show"',
edgecolor=None, shrink=0.6)
ax.set_yticks(list(range(0, 3)), labels=list(range(0, 3)))
plt.show()
По оценке перформанса нельзя определить, что сотрудник покинет компанию по причине 'no-call, no-show'.
plotter(pltype='hist', data=df_left_third_group, x='pay_less_than_median', hue='position', multiple='dodge', xlabel='',
title='Платили ли в компании ушедшим сотрудникам меньше медианы должности?', subtitle='Покинувшие компанию по причинам группы 3 (Try not hire)',
edgecolor=None, shrink=0.6)
plt.show()
8 из 15 человек, которые покинули компанию по причинам 3 группы (постараться не нанимать), получали заработную плату меньше медианы по должности. Возможно, истинная причина их ухода крылась в низкой оплате их труда.
# рассчитаем наиболее часто встречающиеся признаки людей, уволенных по причинам 3 группы
df_left_third_group_profile = pd.read_sql(''' with ages as (SELECT DISTINCT
age, CASE WHEN age <= 17 THEN '17-'
WHEN age BETWEEN 18 AND 24 THEN '18-24'
WHEN age BETWEEN 25 AND 29 THEN '25-29'
WHEN age BETWEEN 30 AND 34 THEN '30-34'
WHEN age BETWEEN 35 AND 39 THEN '35-39'
WHEN age BETWEEN 40 AND 44 THEN '40-44'
WHEN age BETWEEN 45 AND 49 THEN '45-49'
WHEN age BETWEEN 50 AND 54 THEN '50-54'
WHEN age BETWEEN 55 AND 59 THEN '55-59'
ELSE '60+'
END AS age_group
FROM hr_dataset),
data as (SELECT
CASE
WHEN "Reason For Term" in ('Another position', 'career change', 'relocation out of area', 'return to school', 'military', 'retiring', 'medical issues', 'maternity leave - did not return') THEN '1. Cannot influence'
WHEN "Reason For Term" in ('more money', 'unhappy', 'hours') THEN '2. Can influence'
WHEN "Reason For Term" in ('attendance', 'performance', 'no-call, no-show', 'gross misconduct') THEN '3. Try not hire'
END AS reason_group,
racedesc, count(*) over (partition by racedesc) as total_per_race,
count(*) over (partition by "Manager Name") as total_per_manager,
age_group, count(*) over (partition by age_group) AS total_in_age_group,
sex, count(*) over (partition by sex) AS total_in_gender_group,
maritaldesc, count(*) over (partition by maritaldesc) AS total_in_marital_group
FROM hr_dataset LEFT JOIN ages ON hr_dataset.age = ages.age
)
(SELECT
racedesc AS feature, round(count(*) over (partition by racedesc) / total_per_race::numeric * 100, 2) AS per_cent_of_group
FROM data
WHERE reason_group = '3. Try not hire'
ORDER BY per_cent_of_group DESC LIMIT 1)
UNION
(SELECT
age_group AS feature, round(count(*) over (partition by age_group) / total_in_age_group::numeric * 100, 2) AS per_cent_of_group
FROM data
WHERE reason_group = '3. Try not hire'
ORDER BY per_cent_of_group DESC LIMIT 1)
UNION
(SELECT
sex AS feature, round(count(*) over (partition by sex) / total_in_gender_group::numeric * 100, 2) AS per_cent_of_group
FROM data
WHERE reason_group = '3. Try not hire'
ORDER BY per_cent_of_group DESC LIMIT 1)
UNION
(SELECT
maritaldesc AS feature, round(count(*) over (partition by maritaldesc) / total_in_marital_group::numeric * 100, 2) AS per_cent_of_group
FROM data
WHERE reason_group = '3. Try not hire'
ORDER BY per_cent_of_group DESC LIMIT 1)
''', connect)
df_left_third_group_profile
| feature | per_cent_of_group | |
|---|---|---|
| 0 | 30-34 | 7.95 |
| 1 | Black or African American | 8.77 |
| 2 | Widowed | 12.50 |
| 3 | Male | 5.26 |
Данная таблица отражает самые частые признаки людей, уволенных по причинам 3 группы (не стоило нанимать). Однако, на мой взгляд, не стоит воспринимать данную таблицу как руководство к действию отсеивать всех кандидатов данной группы, так как частота встречаемости даже самых частых характеристик все еще низкая и, скорее всего, на плохой результат влияют не такие общие характеристики, как пол, возраст, раса и семейное положение.
ax = plotter(pltype='bar', data=df_left_third_group.sort_values(by='per_cent_manager'), x='manager', y='per_cent_manager',
ylabel='Процент уволившихся сотрудников от всех', xlabel='',
title='Процент уволившихся сотрудников', subtitle='от всех, работавших под руководством того или иного менеджера (по причинам группы 3 - Try not hire)',
edgecolor=None)
manager_list = df_left_third_group.sort_values(by='per_cent_manager')['manager'].unique().tolist()
ax.set_xticklabels([i.replace(' ', '\n') for i in manager_list])
for container in ax.containers:
for rect, manager in zip(container, manager_list):
height = rect.get_height()
x = rect.get_x() + rect.get_width() / 2
y = rect.get_y() + height
mark = df_left_third_group[df_left_third_group['manager'] == manager]['manager_valcount'].drop_duplicates().squeeze()
ax.text(x, y, mark, ha='center', va='bottom', fontsize=6)
plt.show()
Наиболее часто сотрудники были уволены по причинам 3 группы ("не стоило нанимать") у менеджеров Kelley Spirea и Kissy Sullivanю Скорее всего, стоит уделить больше внимания подбору сотрудников для этих менеджеров.
# рассчитаем текучесть по компании в целом
df_turnover_company = pd.read_sql('''with data_1 as (SELECT date_part('year', "Date of Hire") as hire_year, date_part('year', "Date of Termination") as fire_year
FROM hr_dataset
),
data_2 as (SELECT hire_year as year, count(*) as hired
FROM data_1
GROUP BY hire_year
),
data_3 as (SELECT fire_year as year, count(*) as fired
FROM data_1
GROUP BY fire_year
),
data_4 as (SELECT data_2.year, hired, fired, sum(hired) over w - sum(CASE WHEN fired is NULL THEN 0 END) over w AS at_the_end
FROM data_2 FULL JOIN data_3 ON data_2.year = data_3.year
WINDOW w AS (order by data_2.year rows between unbounded preceding and current row)
)
SELECT *, lag(at_the_end, 1) over (order by year) as at_the_beginning,
round(((CASE WHEN fired is NULL THEN 0 ELSE fired END) / (at_the_end + lag(at_the_end, 1) over (order by year) / 2)) * 100, 2) AS turnover
FROM data_4
order by year
''', connect)
df_turnover_company[:12]
| year | hired | fired | at_the_end | at_the_beginning | turnover | |
|---|---|---|---|---|---|---|
| 0 | 2006.0 | 1.0 | NaN | 1.0 | NaN | NaN |
| 1 | 2007.0 | 2.0 | NaN | 3.0 | 1.0 | 0.00 |
| 2 | 2008.0 | 3.0 | NaN | 6.0 | 3.0 | 0.00 |
| 3 | 2009.0 | 7.0 | NaN | 13.0 | 6.0 | 0.00 |
| 4 | 2010.0 | 9.0 | 2.0 | 22.0 | 13.0 | 7.02 |
| 5 | 2011.0 | 84.0 | 14.0 | 106.0 | 22.0 | 11.97 |
| 6 | 2012.0 | 44.0 | 17.0 | 150.0 | 106.0 | 8.37 |
| 7 | 2013.0 | 44.0 | 15.0 | 194.0 | 150.0 | 5.58 |
| 8 | 2014.0 | 60.0 | 14.0 | 254.0 | 194.0 | 3.99 |
| 9 | 2015.0 | 36.0 | 27.0 | 290.0 | 254.0 | 6.47 |
| 10 | 2016.0 | 14.0 | 14.0 | 304.0 | 290.0 | 3.12 |
| 11 | 2017.0 | 6.0 | NaN | 310.0 | 304.0 | 0.00 |
plotter('line', data=df_turnover_company[:12], x='year', y='turnover', linewidth=3, color=sns.color_palette('magma')[1],
title='Текучесть персонала', subtitle='в целом по компании, 2007-2017гг., в %', xlabel='')
plt.show()
Наибольший уровень текучести в компании наблюдался в 2011 году - 12%, следующий пик текучести случился в 2015 году - 6.5%.
# рассчитаем текучесть по департаментам
df_turnover_department = pd.read_sql('''with
data_1 as (SELECT department, date_part('year', "Date of Hire") as hire_year, date_part('year', "Date of Termination") as fire_year
FROM hr_dataset
),
data_2 as (SELECT department, hire_year as year, count(*) as hired
FROM data_1
GROUP BY department, hire_year
),
data_3 as (SELECT department, fire_year as year, count(*) as fired
FROM data_1
GROUP BY department, fire_year
),
data_4 as (SELECT data_2.department, data_2.year, hired, fired, sum(hired) over w - sum(CASE WHEN fired is NULL THEN 0 ELSE fired END) over w AS at_the_end
FROM data_2 FULL JOIN data_3 ON (data_2.year = data_3.year) AND (data_2.department = data_3.department)
WINDOW w AS (partition by data_2.department order by data_2.year rows between unbounded preceding and current row)
)
SELECT *, lag(at_the_end, 1) over (partition by department order by year) as at_the_beginning,
round(((CASE WHEN fired is NULL THEN 0 ELSE fired END) / (at_the_end + lag(at_the_end, 1) over (partition by department order by year) / 2)) * 100, 2) AS turnover
FROM data_4
ORDER BY department
''', connect)
df_turnover_department[:37]
| department | year | hired | fired | at_the_end | at_the_beginning | turnover | |
|---|---|---|---|---|---|---|---|
| 0 | Admin Offices | 2008.0 | 1.0 | NaN | 1.0 | NaN | NaN |
| 1 | Admin Offices | 2009.0 | 1.0 | NaN | 2.0 | 1.0 | 0.00 |
| 2 | Admin Offices | 2011.0 | 2.0 | NaN | 4.0 | 2.0 | 0.00 |
| 3 | Admin Offices | 2014.0 | 2.0 | NaN | 6.0 | 4.0 | 0.00 |
| 4 | Admin Offices | 2015.0 | 3.0 | 2.0 | 7.0 | 6.0 | 20.00 |
| 5 | Admin Offices | 2016.0 | 1.0 | NaN | 8.0 | 7.0 | 0.00 |
| 6 | Executive Office | 2012.0 | 1.0 | NaN | 1.0 | NaN | NaN |
| 7 | IT/IS | 2010.0 | 2.0 | NaN | 2.0 | NaN | NaN |
| 8 | IT/IS | 2011.0 | 3.0 | NaN | 5.0 | 2.0 | 0.00 |
| 9 | IT/IS | 2012.0 | 3.0 | NaN | 8.0 | 5.0 | 0.00 |
| 10 | IT/IS | 2013.0 | 1.0 | NaN | 9.0 | 8.0 | 0.00 |
| 11 | IT/IS | 2014.0 | 10.0 | NaN | 19.0 | 9.0 | 0.00 |
| 12 | IT/IS | 2015.0 | 20.0 | 7.0 | 32.0 | 19.0 | 16.87 |
| 13 | IT/IS | 2016.0 | 5.0 | 3.0 | 34.0 | 32.0 | 6.00 |
| 14 | IT/IS | 2017.0 | 6.0 | NaN | 40.0 | 34.0 | 0.00 |
| 15 | Production | 2007.0 | 2.0 | NaN | 2.0 | NaN | NaN |
| 16 | Production | 2008.0 | 2.0 | NaN | 4.0 | 2.0 | 0.00 |
| 17 | Production | 2009.0 | 6.0 | NaN | 10.0 | 4.0 | 0.00 |
| 18 | Production | 2010.0 | 6.0 | 2.0 | 14.0 | 10.0 | 10.53 |
| 19 | Production | 2011.0 | 69.0 | 14.0 | 69.0 | 14.0 | 18.42 |
| 20 | Production | 2012.0 | 33.0 | 17.0 | 85.0 | 69.0 | 14.23 |
| 21 | Production | 2013.0 | 37.0 | 13.0 | 109.0 | 85.0 | 8.58 |
| 22 | Production | 2014.0 | 35.0 | 10.0 | 134.0 | 109.0 | 5.31 |
| 23 | Production | 2015.0 | 11.0 | 16.0 | 129.0 | 134.0 | 8.16 |
| 24 | Production | 2016.0 | 7.0 | 11.0 | 125.0 | 129.0 | 5.80 |
| 25 | Sales | 2006.0 | 1.0 | NaN | 1.0 | NaN | NaN |
| 26 | Sales | 2010.0 | 1.0 | NaN | 2.0 | 1.0 | 0.00 |
| 27 | Sales | 2011.0 | 7.0 | NaN | 9.0 | 2.0 | 0.00 |
| 28 | Sales | 2012.0 | 5.0 | NaN | 14.0 | 9.0 | 0.00 |
| 29 | Sales | 2013.0 | 3.0 | NaN | 17.0 | 14.0 | 0.00 |
| 30 | Sales | 2014.0 | 11.0 | 3.0 | 25.0 | 17.0 | 8.96 |
| 31 | Sales | 2015.0 | 2.0 | 1.0 | 26.0 | 25.0 | 2.60 |
| 32 | Sales | 2016.0 | 1.0 | NaN | 27.0 | 26.0 | 0.00 |
| 33 | Software Engineering | 2011.0 | 3.0 | NaN | 3.0 | NaN | NaN |
| 34 | Software Engineering | 2012.0 | 2.0 | NaN | 5.0 | 3.0 | 0.00 |
| 35 | Software Engineering | 2013.0 | 3.0 | 1.0 | 7.0 | 5.0 | 10.53 |
| 36 | Software Engineering | 2014.0 | 2.0 | 1.0 | 8.0 | 7.0 | 8.70 |
plotter('line', data=df_turnover_department[:37], x='year', y='turnover', hue='department',
linewidth=3, title='Текучесть персонала', subtitle='в каждом департаменте, 2007-2017гг., в %', xlabel='')
plt.show()
Высокие значения текучести - до 20% - наблюдаются в 2011 году, в департаменте Production и в 2015 году, в Admin Offices и IT/IS.
# рассчитаем текучесть по менеджеру
df_turnover_manager = pd.read_sql('''with
data_1 as (SELECT "Manager Name" as manager, date_part('year', "Date of Hire") as hire_year, date_part('year', "Date of Termination") as fire_year
FROM hr_dataset
),
data_2 as (SELECT manager, hire_year as year, count(*) as hired
FROM data_1
GROUP BY manager, hire_year
),
data_3 as (SELECT manager, fire_year as year, count(*) as fired
FROM data_1
GROUP BY manager, fire_year
),
data_4 as (SELECT data_2.manager, data_2.year, hired, fired, sum(hired) over w - sum(CASE WHEN fired is NULL THEN 0 ELSE fired END) over w AS at_the_end
FROM data_2 FULL JOIN data_3 ON (data_2.year = data_3.year) AND (data_2.manager = data_3.manager)
WINDOW w AS (partition by data_2.manager order by data_2.year rows between unbounded preceding and current row)
)
SELECT *, lag(at_the_end, 1) over (partition by manager order by year) as at_the_beginning,
round(((CASE WHEN fired is NULL THEN 0 ELSE fired END) / (at_the_end + lag(at_the_end, 1) over (partition by manager order by year) / 2)) * 100, 2) AS turnover
FROM data_4
''', connect)
df_turnover_manager = df_turnover_manager[:107]
df_turnover_manager['year'] = df_turnover_manager['year'].astype('int')
to_filter = df_turnover_manager.groupby('manager').sum().reset_index()
to_filter['mark'] = to_filter['fired'] != 0
df_turnover_manager = df_turnover_manager.merge(to_filter[['manager', 'mark']], how='left', on='manager')
df_turnover_manager = df_turnover_manager[df_turnover_manager['mark']]
df_turnover_manager[df_turnover_manager.manager == 'John Smith']
| manager | year | hired | fired | at_the_end | at_the_beginning | turnover | mark | |
|---|---|---|---|---|---|---|---|---|
| 57 | John Smith | 2010 | 1.0 | NaN | 1.0 | NaN | NaN | True |
| 58 | John Smith | 2011 | 4.0 | NaN | 5.0 | 1.0 | 0.00 | True |
| 59 | John Smith | 2012 | 3.0 | NaN | 8.0 | 5.0 | 0.00 | True |
| 60 | John Smith | 2014 | 5.0 | 2.0 | 11.0 | 8.0 | 13.33 | True |
| 61 | John Smith | 2015 | 1.0 | 1.0 | 11.0 | 11.0 | 6.06 | True |
g = sns.FacetGrid(df_turnover_manager, col='manager', col_wrap=4, hue='manager', palette='magma', sharey=False, sharex=False)
g.map_dataframe(sns.barplot, 'year', 'turnover', linewidth=3, width=0.5)
g.set_titles(col_template="{col_name}")
for ax in g.axes:
ax.tick_params(axis='x', labelrotation=20)
plt.show()
Стоит отметить высокую текучесть, особенно в последний год, сотрудников под руководством менеджера Webster Butler, Amy Dunn и пиковое значение более 60% в 2011 году у David Stanley.
Присвоим каждой оценке перформанса свой вес: чем лучше перформанс, тем выше вес.
1 - PIP
1.5 - Needs Improvement, N/A- too early to review
2 - Fully Meets, 90-day meets
2.5 - Exceeds
3 - Exceptional
Условно, сотрудники, которые полностью удовлетворяют требованиям, стоят двоих, которые находятся на плане по улучшению перформанса и т.д.
Аналогично, переведем количество дней, отработанных в компании, в коэффициенты - от 0 до 7. (Число 7 больше, чем 3, используемое для оценки перформанса, так как Exceptional сотрудник, который мало проработал в компании, скорее всего, приносит меньше пользы, чем тот, кто работает в компании долго)
Минимальное количество отработанных дней - 2
Максимальное количество отработанных дней - 4339
Определим новый показатель - "качество сотрудника":
Качество сотрудника = коэффициент перформанса * коэффициент отработанного в компании времени
Далее сгруппируем по источнику найма, подсчитывая общее кол-во нанятых и суммируя коэффициенты качества сотрудников, нанятых с помощью одного и того же источника, а также проставим метку, является ли источник бесплатным или нет (для дальнейшего разделения).
# рассчитаем метрику "качества" сотрудника и определим суммарное качество по источнику найма
df_source = pd.read_sql('''with data as (SELECT "Employee Source" AS source, "Performance Score" as perf,
CASE WHEN "Performance Score" = 'PIP' THEN 1
WHEN "Performance Score" in ('Needs Improvement', 'N/A- too early to review') THEN 1.5
WHEN "Performance Score" in ('Fully Meets', '90-day meets') THEN 2
WHEN "Performance Score" = 'Exceeds' THEN 2.5
WHEN "Performance Score" = 'Exceptional' THEN 3
END as performance, "Days Employed",
round(("Days Employed"::numeric * 7) / 4339, 2) as experience,
"Total" as recruitment_costs
FROM hr_dataset LEFT JOIN recruiting_costs ON "Employee Source" = "Employment Source")
SELECT source, count(*), sum(performance * experience) AS employee_quality,
avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) as recruitment_costs,
CASE WHEN avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) = 0 THEN 'free' ELSE 'paid_for' END AS cost
FROM data
GROUP BY source
ORDER BY employee_quality DESC
''', connect)
df_source
| source | count | employee_quality | recruitment_costs | cost | |
|---|---|---|---|---|---|
| 0 | Employee Referral | 31 | 131.315 | 0.0 | free |
| 1 | Diversity Job Fair | 29 | 130.040 | 10021.0 | paid_for |
| 2 | Monster.com | 24 | 105.195 | 5760.0 | paid_for |
| 3 | Search Engine - Google Bing Yahoo | 25 | 103.835 | 5183.0 | paid_for |
| 4 | Professional Society | 20 | 95.460 | 1200.0 | paid_for |
| 5 | MBTA ads | 17 | 93.280 | 10980.0 | paid_for |
| 6 | Billboard | 16 | 88.845 | 6192.0 | paid_for |
| 7 | Pay Per Click - Google | 21 | 84.635 | 3509.0 | paid_for |
| 8 | Newspager/Magazine | 18 | 67.385 | 8291.0 | paid_for |
| 9 | Other | 9 | 58.925 | 3995.0 | paid_for |
| 10 | Website Banner Ads | 13 | 56.285 | 7143.0 | paid_for |
| 11 | Glassdoor | 14 | 50.120 | 0.0 | free |
| 12 | On-campus Recruiting | 12 | 47.755 | 7500.0 | paid_for |
| 13 | Vendor Referral | 15 | 47.350 | 0.0 | free |
| 14 | Word of Mouth | 13 | 42.080 | 0.0 | free |
| 15 | Social Networks - Facebook Twitter etc | 11 | 39.710 | 5573.0 | paid_for |
| 16 | Internet Search | 6 | 23.665 | 0.0 | free |
| 17 | Information Session | 4 | 20.260 | 0.0 | free |
| 18 | Indeed | 8 | 7.940 | 0.0 | free |
| 19 | Careerbuilder | 1 | 7.840 | 7790.0 | paid_for |
| 20 | Company Intranet - Partner | 1 | 1.440 | 0.0 | free |
| 21 | On-line Web application | 1 | 0.620 | 0.0 | free |
| 22 | Pay Per Click | 1 | 0.000 | 1323.0 | paid_for |
ax = plotter('bar',data=df_source, x='employee_quality', y='source', hue='cost', dodge=False,
title='Полезность источников найма', subtitle='по оценке "качества" нанимаемых сотрудников',
xlabel='Суммарное качество нанятых сотрудников', ylabel='')
ax.set_yticklabels([''.join([str(i.get_text())[:23], '...']) if len(str(i.get_text())) > 22 else i.get_text() for i in ax.get_yticklabels()])
plt.show()
Наиболее эффективным с точки зрения "качества" нанятых сотрудников является источник Employee Referral, который к тому же является бесплатным для компании. Однако строки с 2 по 10 по суммарному "качеству" нанимаемых сотрудников занимают уже оплачиваемые источники найма - Diversity Job Fair, Monster.com и др.
# рассчитаем общую полезность платных и бесплатных источников найма
df_free_or_paid = pd.read_sql('''with data_1 as (SELECT "Employee Source" AS source, "Performance Score" as perf,
CASE WHEN "Performance Score" = 'PIP' THEN 1
WHEN "Performance Score" in ('Needs Improvement', 'N/A- too early to review') THEN 1.5
WHEN "Performance Score" in ('Fully Meets', '90-day meets') THEN 2
WHEN "Performance Score" = 'Exceeds' THEN 2.5
WHEN "Performance Score" = 'Exceptional' THEN 3
END as performance, "Days Employed",
round(("Days Employed"::numeric * 7) / 4339, 2) as experience,
"Total" as recruitment_costs
FROM hr_dataset LEFT JOIN recruiting_costs ON "Employee Source" = "Employment Source"),
data_2 as(
SELECT source, count(*), sum(performance * experience) AS employee_quality,
avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) as recruitment_costs,
CASE WHEN avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) = 0 THEN 'free' ELSE 'paid_for' END AS cost
FROM data_1
GROUP BY source
ORDER BY employee_quality DESC
)
SELECT cost, sum(count) as total_hired, sum(employee_quality) as total_quality, round(sum(employee_quality) / sum(count), 2) as average_quality
FROM data_2
GROUP BY cost
''', connect)
df_free_or_paid
| cost | total_hired | total_quality | average_quality | |
|---|---|---|---|---|
| 0 | paid_for | 217.0 | 979.19 | 4.51 |
| 1 | free | 93.0 | 324.79 | 3.49 |
Среднее "качество" сотрудников, нанятых через оплачиваемые источники найма, на балл выше, чем нанятых через неоплачиваемые - что довольно значимая разница.
# рассчитаем стоимость источника найма на единицу качества нанятых сотрудников
df_paid_sources = pd.read_sql('''with data_1 as (SELECT "Employee Source" AS source, "Performance Score" as perf,
CASE WHEN "Performance Score" = 'PIP' THEN 1
WHEN "Performance Score" in ('Needs Improvement', 'N/A- too early to review') THEN 1.5
WHEN "Performance Score" in ('Fully Meets', '90-day meets') THEN 2
WHEN "Performance Score" = 'Exceeds' THEN 2.5
WHEN "Performance Score" = 'Exceptional' THEN 3
END as performance, "Days Employed",
round(("Days Employed"::numeric * 7) / 4339, 2) as experience,
"Total" as recruitment_costs
FROM hr_dataset LEFT JOIN recruiting_costs ON "Employee Source" = "Employment Source")
SELECT source, count(*) as hired, sum(performance * experience) AS employee_quality,
avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) as recruitment_costs,
CASE WHEN sum(performance * experience) = 0 THEN NULL ELSE
round(avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) / sum(performance * experience), 2)
END AS cost_per_quality,
sum(performance * experience) / count(*) AS avg_quality
FROM data_1
GROUP BY source
HAVING avg(CASE WHEN recruitment_costs is NULL THEN 0 ELSE recruitment_costs END) != 0
ORDER BY cost_per_quality DESC''', connect)
df_paid_sources
| source | hired | employee_quality | recruitment_costs | cost_per_quality | avg_quality | |
|---|---|---|---|---|---|---|
| 0 | Pay Per Click | 1 | 0.000 | 1323.0 | NaN | 0.000000 |
| 1 | Careerbuilder | 1 | 7.840 | 7790.0 | 993.62 | 7.840000 |
| 2 | On-campus Recruiting | 12 | 47.755 | 7500.0 | 157.05 | 3.979583 |
| 3 | Social Networks - Facebook Twitter etc | 11 | 39.710 | 5573.0 | 140.34 | 3.610000 |
| 4 | Website Banner Ads | 13 | 56.285 | 7143.0 | 126.91 | 4.329615 |
| 5 | Newspager/Magazine | 18 | 67.385 | 8291.0 | 123.04 | 3.743611 |
| 6 | MBTA ads | 17 | 93.280 | 10980.0 | 117.71 | 5.487059 |
| 7 | Diversity Job Fair | 29 | 130.040 | 10021.0 | 77.06 | 4.484138 |
| 8 | Billboard | 16 | 88.845 | 6192.0 | 69.69 | 5.552812 |
| 9 | Other | 9 | 58.925 | 3995.0 | 67.80 | 6.547222 |
| 10 | Monster.com | 24 | 105.195 | 5760.0 | 54.76 | 4.383125 |
| 11 | Search Engine - Google Bing Yahoo | 25 | 103.835 | 5183.0 | 49.92 | 4.153400 |
| 12 | Pay Per Click - Google | 21 | 84.635 | 3509.0 | 41.46 | 4.030238 |
| 13 | Professional Society | 20 | 95.460 | 1200.0 | 12.57 | 4.773000 |
Наиболее эффективным из оплачиваемых источников по цене 1 единицы "качества" является источник Professional Society, за ним следуют Pay Per Click - Google и Search Engine - Google Bing Yahoo.
Наименее эффективным же является Pay Per Click и Careerbuilder. От Pay Per Click следует отказаться совсем, а Careerbuilder же стоит рассмотерть дополнительно - он принес крайне высокий показатель "качества", возмонжно, такие всокие затраты оправданы для найма топ-менеджмента компании.
Стоит также отметить, что источник Social Networks - Facebook Twitter etc привлекает людей с самым низким средним "качеством", поэтому, вероятно, следует отказаться от этого источника в пользу других.